Trouble with creating a PDF and attaching to email

[RumMajor]

Ok everyone, this is what I'm trying to accomplish,

I am trying to create a macro that will be operated by a button that will format a range, then a selected range is saved as a PDF to any users desktop and THEN attached to an email followed by more formatting inside the excel sheet. I feel like I am close but I am having trouble getting the file to attach properly.

Sub CleanUpPDF()
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each c In Range("AO1:AO138")
If c.Value = x Then Rows(c.Row).Hidden = True
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Dim DeskTop As String
DeskTop = CreateObject("WScript.Shell").SpecialFolders("Desktop")

Sheets("Acc. Generation").Range("A1:AN138").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
DeskTop & Application.PathSeparator & "Acc. Memo" & " - " & "0" & Range("AQ5") & Range("C6").Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set olApp = CreateObject("Outlook.Application")


With olApp.CreateItem(0)
.cc = "dssimagenow.otc.edu"
.Subject = "Acc. Memo"
.Body = "Greetings," & vbLf & vbLf _
& "The report is attached in PDF format." & vbLf & vbLf _
& "Regards,"
.Attachments.Add PDF_File
.Display



End With

' if you want to delete it
'Kill PDF_File

olApp.Quit
Set olApp = Nothing

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select

End Sub


When I run this code I am getting a run time error 404: Cannot add the attachment; no data course was provided." from the line
.Attachments.Add PDF_File

Anyone have an ideas?

 

[John_w]

Just define the PDF_File variable by adding these lines:

Dim PDF_File As String
PDF_File = DeskTop & Application.PathSeparator & "Acc. Memo" & " - " & "0" & Range("AQ5") & Range("C6").Value & ".pdf"

 

[daverunt]

The filename and path are required for attachments.
Not sure if you can generate a PDf using a variable name?

Sheets("Acc. Generation").Range("A1:AN138").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
 DeskTop & Application.PathSeparator & "Acc. Memo" & " - " & "0" & Range("AQ5") & Range("C6").Value & ".pdf", Quality:= _
 xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
 OpenAfterPublish:=False

PDF_File = DeskTop & Application.PathSeparator & "Acc. Memo" & " - " & "0" & Range("A5") & Range("C6").Value & ".pdf"

.......

.Attachments.Add PDF_File

 

[RumMajor]

John, your solution worked beautifully! I do have one more question though. Does anyone know how to set it to automatically add the signature of the person sending it? I have never attempted a macro this complex and I'm having trouble finding the solution. I would love for it to automatically add the default signature of whoever is sending the email.

 

[daverunt]

Have a look here for info re signature sending.

Insert Outlook Signature in mail