Help with Dynamic Emails

[rnick86]

Hello all,

I was creating a VBA code to insert a PDF to an email.

I was able to do it basically, but once I changed up the file name & path to be dynamic, it could not find the PDF document via the new naming criteria. Below is my code.

Sub sendReminderMail()ChDir "C:\Users\rbs\Desktop"


Dim Path As String
Dim filename As String
Dim File As String


Path = "C:\Users\rbs\Desktop\Invoices\"
filename = Range("B1") & Range("C1") & " - " & Range("B9")
File = Path & filename


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
File, 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 = Range("B12")
.ReadReceiptRequested = True
.Subject = "Invoice #" & Range("C1") & " - " & Range("B9")
.Body = "Here is your invoice
myAttachments.Add File
'.Send
.Display
End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub

I require assistance on the line with "myAttachments.Add File"

If anyone can give me assistance with the Syntax needed for this line to run with the dynamic name change, I would greatly appreciate it.

Thank you,

 

[welshgasman]

Well I have never done it like you have. I *always* specify the extension, and for pdf files it will be .pdf.
As you were exporting as a pdf file windows helpfully (or unhelpfully in this case) adds the extension for you, just as it would if you saved it manually.

If you were to save some file without an extension, then windows does not know which program to use to open it with, but as long as you know the name and path of the file, you can locate it.
So all along you were not giving the full correct name to Outlook to locate the file. You and I would know what you meant by the similarity in names, but computers need to be told exactly, at least the ones we use.

Windows by default hides extensions and relies on the icons to show what type of file it is. Being old school, I prefer to see the extension.

https://www.thewindowsclub.com/show-file-extensions-in-windows

You could have save the extension when exporting, something along the lines of

File = Path & filename & ".pdf"

I would prefer that way, so the file always has a valid extension, rather than the quick fix I offered.

HTH

 

[rnick86]

Well I have never done it like you have. I *always* specify the extension, and for pdf files it will be .pdf.
As you were exporting as a pdf file windows helpfully (or unhelpfully in this case) adds the extension for you, just as it would if you saved it manually.

If you were to save some file without an extension, then windows does not know which program to use to open it with, but as long as you know the name and path of the file, you can locate it.
So all along you were not giving the full correct name to Outlook to locate the file. You and I would know what you meant by the similarity in names, but computers need to be told exactly, at least the ones we use.

Windows by default hides extensions and relies on the icons to show what type of file it is. Being old school, I prefer to see the extension.

https://www.thewindowsclub.com/show-file-extensions-in-windows

You could have save the extension when exporting, something along the lines of

File = Path & filename & ".pdf"

I would prefer that way, so the file always has a valid extension, rather than the quick fix I offered.

HTH

Oh my god, thank you so much, that fixed it. Now the button works perfectly!