VBA doesn't attach saved PDF file to outlook

L

Legacy 476985

Guest
Hello all.
I am trying to create a VBA which will save active area as PDF (with cell as a file name) and send an email. All works well - it saves where I need it, with a correct file name, opens Outlook with body text, but it doesn't attach saved PDF file.
What I am missing?


Sub SaveActiveSheetsAsPDFandEmail()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\.... pallet booking form " & Range("G5 ") & " to " & Range(" B21")

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation


Dim fName As String
With ActiveSheet
fName = .Range("g5").Value

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


strbody = "<BODY style = font-size:12pt; font-familt:Calibri>" & _
"Good day, <br><br> Please find collection booking attached. <br><br><br><br>" & _
"Thank you"

On Error Resume Next
With OutMail
.to = "eva@.....co.uk"
.Subject = "Pallet booking " & Range("g5 ") & " to " & Range(" B21") & " For the delivery on " & Range(" H11")
.Display
.HTMLBody = strbody & .HTMLBody
.Attachments.Add "C:\Users\.... pallet booking form " & Range("G5 ") & " to " & Range(" B21").pdf

End With
On Error GoTo 0

Set OutMail = Nothing

End With

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try it with these changes:
VBA Code:
saveLocation = "C:\Users\.... pallet booking form " & Range("G5").Value & " to " & Range("B21").Value & ".pdf"  'with .pdf extension
VBA Code:
.Attachments.Add saveLocation
 
Upvote 0
Try it with these changes:
VBA Code:
saveLocation = "C:\Users\.... pallet booking form " & Range("G5").Value & " to " & Range("B21").Value & ".pdf"  'with .pdf extension
VBA Code:
.Attachments.Add saveLocation
If the Cells refer to Dates you might have to change the savelocation line to:
Otherwise it wont allow you to save the file as you can't use / in filename

VBA Code:
saveLocation = "C:\Users\.... pallet booking form " & Format(Range("G5"), "dd mmm yy") & " to " & Format(Range("B21"), "dd mmm yy") & ".pdf"  'with .pdf extension
 
Upvote 0
Solution
there is no issue with saving pdf file with cell ref or time. It saves everything perfectly, just doesnt attach to outlook
 
Upvote 0
Try to comment out or Remove On Error Resume Next

Rerun the Code to see if there is any error in your code
 
Upvote 0
What was posted should have worked. As shown in #5, do comment out On Error lines to troubleshoot. Use F8 to debug and run line by line.

VBA Code:
.Attachments.Add SaveLocation & ".pdf"

One can test if file exists for the .Add with Dir(). e.g.
Excel Formula:
If Not(Dir(SaveLocation & ".pdf"))="" then .Attachments.Add SaveLocation & ".pdf"
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top