PDA

View Full Version : Macro for adding variably named PDF in email from excel -



joee74
09-16-2013, 02:08 AM
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.


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

Jan Karel Pieterse
09-16-2013, 07:49 AM
Well, the first thing to do is make sure the PDF is created BEFORE you create the email message. I don't have any Notes code lying about to attach files, but that should be straightforward enough as you already have the filename in your current code.

Kenneth Hobs
09-16-2013, 09:47 AM
As JKP said, move your code to create the PDF file to before the email code.

Either review post 35917 or the link referenced, http://www.fabalou.com/VBandVBA/lotusnotesmail.asp, to see the syntax to create the attachment.