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]

Firstly put a Debug.Print File or MSGBOX File to confirm file name is correct

Change your line to .Attachments.Add File

HTH

 

[rnick86]

Firstly put a Debug.Print File or MSGBOX File to confirm file name is correct

Change your line to .Attachments.Add File

HTH

The name always comes out the way I wanted too for example: INVOICE #000001 - Name

I did both the message box and the debug.print, and they did not fix the issue.

 

[welshgasman]

The name always comes out the way I wanted too for example: INVOICE #000001 - Name

I did both the message box and the debug.print, and they did not fix the issue.

That was not meant to fix the issue, just identify that the filename is actually correct.? Only one was needed depending on your preference.
Have you changed the attachment line as I mentioned.?

 

[rnick86]

Yes, I did verify that the file is correct. I added a few more lines onto it now. Here is what I have so far.

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


'MsgBox File


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")
.Body = "Dear " & Range("B8") & ", " & Chr(10) & Chr(10) & _
"The invoice for " & Range("E8") & " is ready to view." & _
Chr(10) & Chr(10) & Range("B2") & Chr(10) & _
" " & Chr(10) & Range("B3") & _
Chr(10) & Range("B4") & Chr(10) & Range("B5")
.Attachments.Add File
'.Send
.Display


End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


'Confirmation Message Box
MsgBox "Email with " & filename & " was sent to customer " & _
Range("B8") & Chr(10) & Chr(10) & _
"Please export the Invoice data now!"


End Sub

The .Attachments.Add File did not fix the issue. Also, I am no longer getting the display for Outlook, not sure if this is because of the new line

 

[welshgasman]

I cannot see any obvious errors.
What is the value of the filename ?

https://stackoverflow.com/questions/37302602/sending-email-attachments-from-excel-via-vba

 

[rnick86]

So this is the path with the file name:

C:\Users\rbs\Desktop\Invoices\Invoice #I000001 - 0

Therefore Path=C:\Users\rbs\Desktop\Invoices\

filename = multiple cells, that then equals = "Invoice #I000001 - 0"

 

[welshgasman]

No extension?

 

[welshgasman]

Just copied and modified your code.

The .To did not work for me, had to use the Value property.

You need the extension for the file as well to have the complete correct name , so it has never been finding the file.
I had to F8 twice before I got the error message of file not found.

Option Explicit


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




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




Path = "C:\temp\"
filename = Range("A1") & Range("B1") & " - " & Range("C1")
File = Path & filename




'MsgBox File




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("B2").Value
.ReadReceiptRequested = True
.Subject = "Invoice #" & Range("C1")
.Body = "Dear " & Range("B8") & ", " & Chr(10) & Chr(10) & _
"The invoice for " & Range("E8") & " is ready to view." & _
Chr(10) & Chr(10) & Range("B2") & Chr(10) & _
" " & Chr(10) & Range("B3") & _
Chr(10) & Range("B4") & Chr(10) & Range("B5")
.Attachments.Add File & ".pdf"
'.Send
.Display




End With




Set OutLookMailItem = Nothing
Set OutLookApp = Nothing




'Confirmation Message Box
MsgBox "Email with " & filename & " was sent to customer " & _
Range("B8") & Chr(10) & Chr(10) & _
"Please export the Invoice data now!"
End Sub

 

[rnick86]

Just copied and modified your code.

The .To did not work for me, had to use the Value property.

You need the extension for the file as well to have the complete correct name , so it has never been finding the file.
I had to F8 twice before I got the error message of file not found.

Option Explicit


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




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




Path = "C:\temp\"
filename = Range("A1") & Range("B1") & " - " & Range("C1")
File = Path & filename




'MsgBox File




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("B2").Value
.ReadReceiptRequested = True
.Subject = "Invoice #" & Range("C1")
.Body = "Dear " & Range("B8") & ", " & Chr(10) & Chr(10) & _
"The invoice for " & Range("E8") & " is ready to view." & _
Chr(10) & Chr(10) & Range("B2") & Chr(10) & _
" " & Chr(10) & Range("B3") & _
Chr(10) & Range("B4") & Chr(10) & Range("B5")
.Attachments.Add File & ".pdf"
'.Send
.Display




End With




Set OutLookMailItem = Nothing
Set OutLookApp = Nothing




'Confirmation Message Box
MsgBox "Email with " & filename & " was sent to customer " & _
Range("B8") & Chr(10) & Chr(10) & _
"Please export the Invoice data now!"
End Sub

Thank you for all the help, and Happy New Year!

I'm a little confused as this is some of the first time I work with VBA. So how do I find the extension?