Macro for adding variably named PDF in email from excel -
I have written a macro that when I am pricing for a client, I click one button on the page and the following happens.
1. It saves the internal prices from the sheet called "internal" as a PDF, naming that PDF according to the data in cell "BD2".
2. It saves a PDF of the client prices from a sheet called "Client Quote" as a PDF, naming that PDF according to the data in cell "AY8"
3. It opens up a new mail in LotusNotes, inserting TO: email and CC:emails. It then inserts a subject title, also taken from cell "AY8" and body text and signature from various cells within the workbook.
I have all of this bit working now.
I now need it to attach the created "client price" PDF to the email also, but for the life of me I cant work out how to do that. I have tried recording macros and copying that code into this macro but I am completely lost.
Any help you can give me would be very much appreciated.
I am using Excel 2010 and Windows 7.
Code:
Sub Save_and_email_PDF()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim subject As String
Dim EmailAddress As String
Dim s(1 To 21) As String
subject = Worksheets("Client Quote").Range("AY8")
EmailAddress = Worksheets("Client Quote").Range("ay10")
'Debug.Print subject
Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GETDATABASE("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
Set NDoc = NDatabase.CREATEDOCUMENT
With NDoc
.SendTo = EmailAddress
.CopyTo = "test@test.com, " & "test1@test.com, " & "test2@test.com"
.subject = subject
s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
s(2) = ""
s(3) = "Many Thanks for your enquiry"
s(4) = ""
s(5) = "Please find attached your quotation for your request" & " " & " : - " & "'" & Worksheets("internal").Range("j14") & "'"
s(6) = ""
s(7) = "If you would like to go ahead with this order, please let me know and I will send you a template, artwork guidelines and procedures for processing your order."
s(8) = ""
s(9) = "Kind Regards"
s(10) = ""
s(11) = Worksheets("sheet3").Range("a58")
s(12) = Worksheets("sheet3").Range("a59")
s(13) = ""
s(14) = "Z-CARD® PocketMedia® Solutions"
s(16) = ""
s(17) = Worksheets("sheet3").Range("a61")
s(18) = Worksheets("sheet3").Range("a62")
s(19) = ""
s(20) = "7-11 St. Johns Hill, London, SW11 1TN"
s(21) = "The PocketMedia® Agency | www.zcard.co.uk"
.body = Join(s, vbCrLf) & " "
.Save True, False
End With
NUIWorkSpace.EDITDOCUMENT True, NDoc
Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
Worksheets("client quote").Range("AY8").Value & ".pdf", _
OpenAfterPublish:=True
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\joeellis\Desktop\Digital Quotes\Internal Prices\" & _
ActiveSheet.Range("BD2").Value & ".pdf", _
OpenAfterPublish:=False
End Sub
