Search code examples
excelbatch-filethunderbirdvba

Email Attachment only working when statically coded - Excel Visual Basic

Everything is working for me in sending an email with an attachment from Excel using Visual Basic via Thunderbird when the attachment path is hard-coded. 

attachment=C:\Users\Desktop2017\Desktop\customer\customerNumber\invoiceNumber.pdf"

But I need to change part of the file path for attachment based on what's in cell M4 and have the file name change based on what's in cell J4.

Example: M4 value is currently 101. J4 value is currently 2000-01. The output should be "C:\Users\Desktop2017\Desktop\customer\101\2000-01.pdf"

I have tried using 'Range' to get the value and setting a string but instead of getting the data from the cell or string it just outputs whatever I have after the equals sign.

I've tried adding and moving quotation marks around but nothing has worked at this point.

Thanks in advance for any help, Dalton.

PS: Sorry for hobbled together code.

Private Sub EmailInvoice_Click()

Dim FileNumber As Integer
Dim retVal As Variant
Dim strName As String
Dim strFile As String
Dim wsCustomer As Worksheet


strName = Range("Q2").Value
strFile = Dir(strFolder & "*.xlsx")
Const MY_FILENAME = "C:\Users\Desktop2017\Dropbox\temp\invoice.BAT"


FileNumber = FreeFile

 'create batch file
Open MY_FILENAME For Output As #FileNumber
Print #FileNumber, "cd ""C:\Program Files (x86)\Mozilla Thunderbird"""
Print #FileNumber, "thunderbird -compose"; _
" to=" + ThisWorkbook.Sheets("hourlyInvoice01").Range("N21") _
+ ",subject=Invoice " + ThisWorkbook.Sheets("hourlyInvoice01").Range("J4") + ",format="; 1; _
",body=""<HTML><BODY>Hello    "; ThisWorkbook.Sheets("hourlyInvoice01").Range("N20") _
+ "&#44<BR><BR>Please see attached.<BR><BR>Thanks&#44 Dalton.<BR><BR><BR>Contact Info Text Line 1<BR>Contact Info Text Line 2<BR>Contact Info Text Line 3</BODY></HTML>"",attachment=C:\Users\Desktop2017\Desktop\test\script\someFile.txt"

Print #FileNumber, "exit"
Close #FileNumber

 'run batch file
retVal = Shell(MY_FILENAME, vbNormalFocus)

 ' NOTE THE BATCH FILE WILL RUN, BUT THE CODE WILL CONTINUE TO RUN.
If retVal = 0 Then
    MsgBox "An Error Occured"
    Close #FileNumber
    End
End If

 'Delete batch file
'Kill MY_FILENAME

End Sub
Solution

  • Add this before that line

    Dim FlName As String

FlName = "C:\Users\Desktop2017\Desktop\customer\" & Range("M4").Value & "\" & Range("J4").Value & ".pdf"

    and then change the line

    "&#44<BR><BR>Please see attached.<BR><BR>Thanks&#44 Dalton.<BR><BR><BR>Contact Info Text Line 1<BR>Contact Info Text Line 2<BR>Contact Info Text Line 3</BODY></HTML>"",attachment=C:\Users\Desktop2017\Desktop\test\script\someFile.txt"

    to

    "&#44<BR><BR>Please see attached.<BR><BR>Thanks&#44 Dalton.<BR><BR><BR>Contact Info Text Line 1<BR>Contact Info Text Line 2<BR>Contact Info Text Line 3</BODY></HTML>"",attachment=" & FlName

    Note: To concatenate strings, use & instead of +