joee74
09-13-2013, 12:57 PM
Hi
Firstly I have been doing VBA for a week now, so please be gentle with me.
I have a problem I cant find a solution for, despite hours of googling and searching.
I am running a pricing sheet through excel, that when I click on a macro button, it saves the sheet as a PDF, named variably depending on the contents of a cell. This macro also then opens up a new mail in Lotus Notes and adds variable information into the subject and body text, depending on the contents of the same cell. So far this is doing exactly what I want it to, after many many hours of google and forums and a lot of trial and error.
However there are two issues I simply cant get to work.
1. I want to automatically attach the PDF I have created to the email.
The code I have for creating this PDF is :
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="T:\PRODUCTION\Digital Quotes\Client Prices\Quotation_" & _
ActiveSheet.Range("AY8").Value & ".pdf", _
OpenAfterPublish:=True
2. I want to add a signature on every mail, variable dependent on the person using the sheet. I have the ability to save their signature.html in the same location on each users computer, but obviously the user name will change.
The location of this signature.html file will be as follows (C:\Users\"user-variable"\Documents\"username-variable"-sig.html)
The code I have that is working as far as I can get it is as follows:
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 5) As String
subject = Worksheets("Internal").Range("BD1")
EmailAddress = Worksheets("Internal").Range("BD2")
'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 = "digitalprint@zcard.com"
.subject = subject
s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
s(2) = "Many Thanks for your enquiry"
s(3) = "Please find Attached your Quotation"
s(4) = "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(5) = " "
.body = Join(s, vbCrLf & vbCrLf) & _
NDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
.Save True, False
End With
NUIWorkSpace.EDITDOCUMENT True, NDoc
Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="T:\PRODUCTION\Digital Quotes\Client Prices\Quotation_" & _
ActiveSheet.Range("AY8").Value & ".pdf", _
OpenAfterPublish:=True
End Sub
any help would be massively appreciated as I am seeing a long long weekend ahead
Firstly I have been doing VBA for a week now, so please be gentle with me.
I have a problem I cant find a solution for, despite hours of googling and searching.
I am running a pricing sheet through excel, that when I click on a macro button, it saves the sheet as a PDF, named variably depending on the contents of a cell. This macro also then opens up a new mail in Lotus Notes and adds variable information into the subject and body text, depending on the contents of the same cell. So far this is doing exactly what I want it to, after many many hours of google and forums and a lot of trial and error.
However there are two issues I simply cant get to work.
1. I want to automatically attach the PDF I have created to the email.
The code I have for creating this PDF is :
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="T:\PRODUCTION\Digital Quotes\Client Prices\Quotation_" & _
ActiveSheet.Range("AY8").Value & ".pdf", _
OpenAfterPublish:=True
2. I want to add a signature on every mail, variable dependent on the person using the sheet. I have the ability to save their signature.html in the same location on each users computer, but obviously the user name will change.
The location of this signature.html file will be as follows (C:\Users\"user-variable"\Documents\"username-variable"-sig.html)
The code I have that is working as far as I can get it is as follows:
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 5) As String
subject = Worksheets("Internal").Range("BD1")
EmailAddress = Worksheets("Internal").Range("BD2")
'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 = "digitalprint@zcard.com"
.subject = subject
s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
s(2) = "Many Thanks for your enquiry"
s(3) = "Please find Attached your Quotation"
s(4) = "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(5) = " "
.body = Join(s, vbCrLf & vbCrLf) & _
NDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
.Save True, False
End With
NUIWorkSpace.EDITDOCUMENT True, NDoc
Set NDoc = Nothing
Set WordApp = Nothing
Set NSession = Nothing
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="T:\PRODUCTION\Digital Quotes\Client Prices\Quotation_" & _
ActiveSheet.Range("AY8").Value & ".pdf", _
OpenAfterPublish:=True
End Sub
any help would be massively appreciated as I am seeing a long long weekend ahead