PDF to Email

[jcbaggett16]

All,

I am trying to save a file to PDF then pull that file and attach to email. I can get it to save to a file path but I can not get it to pull from the file path.

Sub PDF()
ChDir "C:\Users\...\Desktop\New folder"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\...\Desktop\New folder\Transition " & Range("L2").Text


Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments

With OutLookMailItem
.To = ""
.Subject = ""
.Body = ""
myAttachments.Add "C:\Users\...\Desktop\New folder\Test.PDF"
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub

 

[dermie_72]

Try adding
Dim Strfilename as String
Strfilename = "C:\Users\...\Desktop\New folder\Test.PDF" 'above the with block

Then changing myattachements.add to
.Attachments.add Strfilename

 

[jcbaggett16]

Try adding
Dim Strfilename as String
Strfilename = "C:\Users\...\Desktop\New folder\Test.PDF" 'above the with block

Then changing myattachements.add to
.Attachments.add Strfilename

This is what I have Now,

Sub PDF()
ChDir "C:\Users\Desktop\New folder"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Desktop\New folder\Transition " & Range("L2").Text


Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim Attachments As Object
Dim Strfilename As String
Strfilename = "C:\Users\Desktop\New folder\Transition " & Range("L2").PDF

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set Attachments = OutLookMailItem.Attachments

With OutLookMailItem
.To = ""
.Subject = ""
.Body = ""
Attachments.Add Strfilename
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub

 

[dermie_72]

.Attachments.Add

You need the full stop first.
Also when testing this, can you confirm that the file exists in the location you provided.
When running the macro, confirm that Range("L2") has a value at the time of the Strfilename
Do this in the immediate window as add a debug.print Range("L2").Value just before Strfilename = "C:\Users\Desktop\New folder\Transition " & Range("L2").PDF

 

[jcbaggett16]

.Attachments.Add

You need the full stop first.
Also when testing this, can you confirm that the file exists in the location you provided.
When running the macro, confirm that Range("L2") has a value at the time of the Strfilename
Do this in the immediate window as add a debug.print Range("L2").Value just before Strfilename = "C:\Users\Desktop\New folder\Transition " & Range("L2").

 

[jcbaggett16]

Got it to work, now it only attaches to outlook only if outlook is already open. How would I get it to open outlook if its not already open?

Sub PDF()
ChDir "C:\Users\...\Desktop\New folder"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\...\Desktop\New folder\Transition " & Range("H2").Text


Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments

With OutLookMailItem
.To = ""
.Subject = ""
.Body = ""
myAttachments.Add "C:\Users\...\Desktop\New folder\Transition " & Range("H2").Text & ".pdf"
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub

 

[dermie_72]

How to test if Outlook is open?

How do I test for Outlook, to bypass opening. if outlook.application "is available" then 'run the command for sending an email else 'open/display the current users document fo...

stackoverflow.com