brianfosterblack
Active Member
- Joined
- Nov 1, 2011
- Messages
- 251
I have an invoice in my workbook which my code saves as a pdf document and then emails it as an attachment.
My problem is I have the directory of the pdf hard coded into my code so when I give this workbook to someone else it does not work on their computer
I currently save the invoice in the same directory as the workbook with the same name as the workbook and then send the mail and thereafter for each other invoice I just overwrite the pdf with the next invoice and email each in turn (Much of this code was provided by Mr Excel)
My problem is I have the directory of the pdf hard coded into my code so when I give this workbook to someone else it does not work on their computer
I currently save the invoice in the same directory as the workbook with the same name as the workbook and then send the mail and thereafter for each other invoice I just overwrite the pdf with the next invoice and email each in turn (Much of this code was provided by Mr Excel)
VBA Code:
Dim emailApplication As Object
Dim emailItem As Object
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
' save the file as pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\User\Documents\Clay\2021 SA Grand\Entries.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Sheets("Receipt").Select
If Range("Receipt!L37").Value = "" Then
MsgBox (Range("Receipt!C46").Value & " " & "has no e-mail address")
Exit Sub
End If
' Now we build the email.
emailItem.to = Range("Receipt!L37").Value
emailItem.Subject = "SA Grand 2021 Entry and Invoice"
emailItem.Body = "Thank you for your entry in the SA Grand 2021 at ,y club" & vbCr & vbCr & "Please check that you have been entered correctly for this event." & vbCr & vbCr & "Your squad details, time and range will be added the Sunday before the competition and sent to you." & vbCr & vbCr & "The invoice for the entry is on the bottom of the attachment." & vbCr & vbCr & "Regards" & vbCr & " My name" & vbCr & "My number"
' Attach current Workbook
' emailItem.Attachments.Add ActiveWorkbook.FullName
' Attach any file from your computer.
emailItem.Attachments.Add ("C:\Users\User\Documents\Clay\2021 SA Grand\Entries.pdf")
' Send the Email
emailItem.Send
Set emailItem = Nothing
Set emailApplication = Nothing
End Sub