I had an earlier problem with part of a macro not correctly recording files saved as pdf in a worksheet list, but is fixed now and can be seen in the code to follow. After saving as a pdf and recording the save in a worksheet list, the macro is supposed to email the file via Outlook. From a control button placed in the worksheet of the invoice and assigned to the macro, creating the invoice as a pdf, saving it to the correct folder and recording all that, the emailing does not work. I'm logged into Outlook. When the macro is run, each time it hangs up and the debug shows the From address line highlighted in yellow. In Outlook directly even with Excel and the macro closed, nothing happens when typing in an address, or subject or body of text. I'm on the DuckDuckGo browser. Here is my vba for this macro.
Sub Save_as_PDF_and_Email()
'// Defines static part of file path.
Const ROOT As String = "E:\Mem Inv Rec\"
'// Declares dimensions of variables.
Dim MemID As String
Dim MemName As String
Dim MemEAddr As String
Dim InvDate As String
Dim InvMonth As String
Dim InvYear As String
Dim xOutApp As Object
Dim xOutMail As Object
Dim RootPath As String
Dim FilePath As String
Dim Attachment As String
Dim NextFile As Range
'// Set locations and formatting of variables.
MemID = Range("C12")
InvDate = Range("H12") 'Formatted as Mmm dd, yyyy
InvDate = Format(InvDate, "mm-dd-yy") 'Formatted for file name
InvYear = Year(InvDate) 'Formatted for folder in the file path
InvMonth = Month(InvDate) 'Formatted for email subject
MemName = Range("E10")
MemEAddr = Range("E9").Value
RootPath = ROOT & MemID & "\" & InvYear & "\"
FilePath = InvDate & "---" & MemID & ".pdf"
Attachment = RootPath & FilePath
'// Creates file as .pdf, defines parameters and saves file.
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
IgnorePrintAreas:=False, _
FileName:=RootPath & FilePath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
OpenAfterPublish:=False
'// Creates record of billing of saved file in Record of Billing worksheet.
Set NextFile = Sheet3.Range("A150").End(xlUp).Offset(1, 0)
NextFile = InvDate
NextFile.Offset(0, 1) = MemID
NextFile.Offset(0, 3) = Now
'// Creates Hyperlink in Record of Billing worksheet.
Sheet3.Hyperlinks.Add _
Anchor:=NextFile.Offset(0, 2), _
Address:=RootPath & FilePath & ".pdf"
'//Creates Email
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
'// Configure email.
With xOutMail
.From = "sender@outlook.com"
.To = "MemEAddr"
.CC = ""
.BCC = ""
.Subject = InvMonth & " Invoice"
.Body = "Dear " & MemName & "," & _
vbNewLine & vbNewLine & _
"Please see attached" & _
vbNewLine & vbNewLine
.Attachments.Add Attachment
.Send
.MsgBox "Email sent", vbInformation
End With
'//Clean email fields.
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub