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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
366
Domenic
D
B
  • Question Question
font size in outlook email (12 points doesn't work)
2
Replies
13
Views
272
basb87
B
P
  • Solved
What does this code do or mean
Replies
2
Views
151
Perrister
P
S
  • Solved
Tweak to VBA email code
Replies
3
Views
718
rollis13
rollis13
G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
456
GarnesGambit
G

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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