VBA doesn't attach saved PDF file to outlook

  • Thread starter Thread starter Legacy 476985
  • Start date Start date
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
John_w said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
496
Domenic
D
A
  • Question Question
Add Outlook "Confidential" Sensitivity Label
Replies
2
Views
212
Aviles
A
Sweedler
  • Question Question
VBA export PDF, attach to email and send email
2
Replies
13
Views
252
Peter_SSs
Peter_SSs
C
  • Question Question
VBA to save file to SharePoint and attach to outlook template saved in SharePoint
Replies
6
Views
283
Chris8630
C
J
  • Question Question
Send bulk email attachment error
Replies
3
Views
115
JOHATUP
J

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

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