I need some help with my vba code i have a spreadsheet created as an auto invoice that saves as a pdf under a file path according to the invoice number in cell k9 and i am trying to automate the emailing of the invoice to clients email address based in cell h15 but it continues to get stuck on the attachments file path
Please Help
See Code below
Sub create_and_email_pdf()
Dim EmailSubject As String, EmailSignature As String
Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OutlookApp As Object, OutlookMail As Object
CurrentMonth = ""
' *****************************************************
' ***** You Can Change These Variables *********
EmailSubject = "Peachy Pools Invoice " 'Change this to change the subject of the email. The current month is added to end of subj line
OpenPDFAfterCreating = True 'Change this if you want to open the PDF after creating it : TRUE or FALSE
AlwaysOverwritePDF = False 'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
DisplayEmail = True 'Change this if you don't want to display the email before sending. Note, you must have a TO email address specified for this to work
Email_To = ActiveSheet.Range("H15") 'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
Email_CC = ""
Email_BCC = ""
' ******************************************************
Dim NewFN As Variant
' COPY INVOICE TO A NEW WORKBOOK
NewFN = "C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
'Create an Outlook object and new mail message
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
'Display email and specify To, Subject, etc
With OutlookMail
.Display
.To = Email_To
.CC = Email_CC
.BCC = Email_BCC
.Subject = EmailSubject
.Attachments.Add = Filename:="C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value
If DisplayEmail = False Then
.Send
End If
End With
ActiveSheet.PrintOut , Copies:=1
Range("k9").Value = Range("k9").Value + 1
Range("b18:h32").ClearContents
Range("j18:j32").ClearContents
Range("j10:k10").ClearContents
ActiveWorkbook.Save
End Sub
Bookmarks