VBA doesn't attach saved PDF file to outlook

evazidz

New Member
Joined
Apr 9, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
418
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Solution

evazidz

New Member
Joined
Apr 9, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
there is no issue with saving pdf file with cell ref or time. It saves everything perfectly, just doesnt attach to outlook
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
418
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try to comment out or Remove On Error Resume Next

Rerun the Code to see if there is any error in your code
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,174
Office Version
  1. 365
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

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
Top