Macro for adding variably named PDF in email from excel -

[joee74]

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]

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]

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.