• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Save Excel File as pdf with name of a cell and then email file

FaithAnnie

New Member
I have a excel worksheet and have wrote the VBA to save as a pdf, with the name of a particular cell. The VBA is saving as the pdf in the location. But i am having issues getting to following-up sending by email the file. I keep getting an error.
Does anyone see what i need to fix?

Code:
Sub sendReminderMail()
ChDir "C:\Total Rewards"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Total Rewards\" & ActiveSheet.Range("A2").Value & ".pdf", OpenAfterPublish:=True

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 = ActiveSheet.Range("C32")
.Subject = ActiveSheet.Range("A1")
.Body = "Here is your 2020 Total Rewards Statement."
myAttachments.Add "C:\Total Rewards\"& ActiveSheet.Range("A2").Value"      This is where the error is showing.
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub
 
Last edited by a moderator:

Logit

Active Member
See if this works :

Code:
Option Explicit

Sub sendReminderMail()
ChDir "C:\Total Rewards\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Total Rewards\" & ActiveSheet.Range("A2").Value & ".pdf", OpenAfterPublish:=False

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

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


With OutLookMailItem
.To = ActiveSheet.Range("C32").Value
.Subject = ActiveSheet.Range("A1").Value
.Body = "Here is your 2020 Total Rewards Statement."
.Attachments.Add "C:\Total Rewards\" & ActiveSheet.Range("A2").Value & ".pdf"    'This is where the error is showing.
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub

Note that I change the OpenAfterPublish to False.
 
Top