h3rm1t9536
asked on
Macro for attaching PDF to Lotus Notes email. PDF name is variable.
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 Notes, 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 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.
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 Notes, 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 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.
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 SubASKER
Hi fp
I have put the code in as follows and its still not attaching the PDF.
Did I put the code correctly?
Its all very new to me using excel in this way.
I have put the code in as follows and its still not attaching the PDF.
Did I put the code correctly?
Its all very new to me using excel in this way.
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
Dim objAttach As Object
Dim objEmbed As Object
Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\joeellis\Desktop\Digital Quotes\2. Client Prices\" & _
Worksheets("client quote").Range("AY8").Value & ".pdf", _
OpenAfterPublish:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\joeellis\Desktop\Digital Quotes\3. Internal Prices\" & _
ActiveSheet.Range("BD2").Value & ".pdf", _
OpenAfterPublish:=False
subject = Worksheets("Client Quote").Range("AY8")
EmailAddress = Worksheets("Client Quote").Range("f3") & " " & "<" & Worksheets("Client Quote").Range("ay10") & "> ,"
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 = "digitalprint@zcard.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
Set objAttach = NDoc.CreateRichTextItem("attachment1")
Set objEmbed = objAttach.EmbedObject(1454&, _
"attachment1", _
"C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
Worksheets("client quote").Range("AY8").Value & ".pdf", _
"")
NUIWorkSpace.EDITDOCUMENT True, NDoc
Set objAttach = Nothing
Set objEmbed = Nothing
Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
End Sub![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi again,
All looks OK, about from, perhaps, the order of the saving of the e-mail & the attaching of the file.
I would perhaps try this (snippet of the full code) re-ordering...
I have simply moved the .Save & End With statements beneath the two Set statements for the new objects.
Like I said, I am probably far from the best candidate to assist here (given my lack of access to the same environment), so if we are not successful I can post a comment to some of the (more) frequent "Experts" within the Microsoft Excel Topic Area to see if any have (more recent) experience than I do, &/or can assist further.
Fingers-crossed for you here, though :)
All looks OK, about from, perhaps, the order of the saving of the e-mail & the attaching of the file.
I would perhaps try this (snippet of the full code) re-ordering...
With NDoc
.SendTo = EmailAddress
.CopyTo = "digitalprint@zcard.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) & " "
Set objAttach = NDoc.CreateRichTextItem("attachment1")
Set objEmbed = objAttach.EmbedObject(1454&, _
"attachment1", _
"C:\Users\joeellis\Desktop\Digital Quotes\Client Prices\" & _
Worksheets("client quote").Range("AY8").Value & ".pdf", _
"")
.Save True, False
End WithI have simply moved the .Save & End With statements beneath the two Set statements for the new objects.
Like I said, I am probably far from the best candidate to assist here (given my lack of access to the same environment), so if we are not successful I can post a comment to some of the (more) frequent "Experts" within the Microsoft Excel Topic Area to see if any have (more recent) experience than I do, &/or can assist further.
Fingers-crossed for you here, though :)
ASKER
Hi fp
Its still not working, but I have noticed something that may be having a bearing on this function.
When I am saving the variable named PDF in step 1, it is saving to the destination folder and naming as it should.
However, when I then drag and drop that file into our company MIS program, its dropping the file in, but only picking up the name "Quotation No" and not the variable elements.
Could this be the problem in as much as the macro is looking for a file it cant find?
Its still not working, but I have noticed something that may be having a bearing on this function.
When I am saving the variable named PDF in step 1, it is saving to the destination folder and naming as it should.
However, when I then drag and drop that file into our company MIS program, its dropping the file in, but only picking up the name "Quotation No" and not the variable elements.
Could this be the problem in as much as the macro is looking for a file it cant find?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi fp
Yes thats worked, so it is a naming problem, however I do need each quote to be named as per the cell reference I have stated.
How do I make that happen?
Yes thats worked, so it is a naming problem, however I do need each quote to be named as per the cell reference I have stated.
How do I make that happen?
ASKER
I should have that the cell the data is being picked up from has the following formula to create that name:
="Quotation No."&H8&" - "&F4&" - "&H7&" - "&G16
Although in windows explorer the full file name is showing as I want it to, when I drop the file into our MIS system in work, its only picking up the "Quotation No." aspect of the name.
I hope I am making sense to you here. as I said, when running it as a fixed name, it works, but I need the naming of the file to be variable.
="Quotation No."&H8&" - "&F4&" - "&H7&" - "&G16
Although in windows explorer the full file name is showing as I want it to, when I drop the file into our MIS system in work, its only picking up the "Quotation No." aspect of the name.
I hope I am making sense to you here. as I said, when running it as a fixed name, it works, but I need the naming of the file to be variable.
ASKER
Hi fp
I have managed to work this out, What I have done is placed the naming location and variable as a string, then referred to that in the naming process and attaching functions and it all works.
Many thanks for your help as you really did point me in the right direction and help me in some way understand what was going on here.
I have attached the code so you can see what I did.
I have managed to work this out, What I have done is placed the naming location and variable as a string, then referred to that in the naming process and attaching functions and it all works.
Many thanks for your help as you really did point me in the right direction and help me in some way understand what was going on here.
I have attached the code so you can see what I did.
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
Dim objAttach As Object
Dim objEmbed As Object
Dim Quotename As String
Dim Qoutename2 As String
subject = Worksheets("Client Quote").Range("AY8")
EmailAddress = Worksheets("Client Quote").Range("f3") & " " & "<" & Worksheets("Client Quote").Range("ay10") & "> ,"
Quotename = "C:\Users\joeellis\Desktop\Digital Quotes\2. Client Prices\" & "Quotation No." & Range("j7").Value & " - " & Range("j11").Value & " - " & Range("j14").Value & " - " & Range("j15").Value
Quotename2 = "C:\Users\joeellis\Desktop\Digital Quotes\3. Internal Prices\" & "Internal No." & Range("j7").Value & " - " & Range("j11").Value & " - " & Range("j14").Value & " - " & Range("j15").Value
Worksheets("client quote").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Quotename, _
OpenAfterPublish:=True
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Quotename2, _
OpenAfterPublish:=False
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 = "digitalprint@zcard.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) & " "
Set objAttach = NDoc.CreateRichTextItem("attachment1")
Set objEmbed = objAttach.EmbedObject(1454&, "attachment1", Quotename & ".pdf")
.Save True, False
End With
NUIWorkSpace.EDITDOCUMENT True, NDoc
Set objAttach = Nothing
Set objEmbed = Nothing
Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
End SubASKER
I eventually worked this one out, but without help I would have not known where to start.
Great news!
Thanks for closing the question in my favour :)
We (jointly) got there in the end. Sorry I didn't respond as quickly as you were working today.
It looks like the use of range/cell objects in the parameters to the Lotus Notes function "EmbedObject" were causing a problem, & using a string variable resolved the issue.
Thanks for also posting the resultant code you are using & this may help others will similar issues in the future.
Good luck with the rest of your project.
BFN,
fp.
Thanks for closing the question in my favour :)
We (jointly) got there in the end. Sorry I didn't respond as quickly as you were working today.
It looks like the use of range/cell objects in the parameters to the Lotus Notes function "EmbedObject" were causing a problem, & using a string variable resolved the issue.
Thanks for also posting the resultant code you are using & this may help others will similar issues in the future.
Good luck with the rest of your project.
BFN,
fp.
I have not used Lotus Notes for quite some time, but I believe to add an attachment the Visual Basic for Applications code is something like this:
Open in new window
Where NDoc is as you defined in your code: Set NDoc = NDatabase.CREATEDOCUMENT
The 'Dim' statements can go at the top of your subroutine (with the other statements of this type).
The two other lines (Set obj...) should go before this statement in your existing code:
NUIWorkSpace.EDITDOCUMENT True, NDoc
However, I see in your code listing that you create the ".pdf" file after the e-mail has been created.
You would need to create the required attachment prior to the e-mail being created (& before the code I suggested is executed).
It would also be 'tidy' to set the two new objects to Nothing, prior to the conclusion of the routine (like you have already for three other objects):
Open in new window
I hope that made sense! :)
Like I said, I have not used Lotus Notes recently, & do not have the provision to do so now, so the parameters to the EmbedObject() method may not be exactly what is required (as I cannot test locally).
Please do let me know what the outcome of the proposal is though.
Thank you.
BFN,
fp.