Poundland
08-15-2008, 03:30 AM
Guys,
I have some code which I use to send an email through Lotus Notes with a document attached, the code allows me to add text to the subject, and body of the email.
What I would like to do is use the contents of some cells in the attachment which forms a graph and include these in the body of the email, so when the recipient receives the email they will see the attachment, subject line text, body text and also the chart.
Is this possible?
Below is my code.
Sub email()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant
Dim stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant
Const EMBED_ATTACHMENT As Long = 1454
' Starts the For Next loop to assign variable email target
For e = 1 To 3
If e = 1 Then vaRecipient = "scott.atkinson@poundland.co.uk"
If e = 2 Then vaRecipient = "neil.maher@poundland.co.uk"
If e = 3 Then vaRecipient = "helen.allsop@poundland.co.uk"
On Error GoTo SendMailError
vaMsg = "THIS AN AUTOMATED EMAIL ----- Please find attached todays Essentials Availability Report, this report includes RZ and Decon data - - - Please review and add action comments to the master file located at I:\H925 Buying\BuyingTeamsALL\New Essentials Availability Report\2008-2009"
stSubject = "*** Essentials Availability Report ***"
stAttachment = "I:\H925 Buying\Scott Atkinson\Projects\Helen's Availability Project\Essentials Availability.xls"
'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
End With
'Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With
Next e
GoTo line1
SendMailError:
Dim Msg
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
'SendMail = False
line1:
End Sub
I have some code which I use to send an email through Lotus Notes with a document attached, the code allows me to add text to the subject, and body of the email.
What I would like to do is use the contents of some cells in the attachment which forms a graph and include these in the body of the email, so when the recipient receives the email they will see the attachment, subject line text, body text and also the chart.
Is this possible?
Below is my code.
Sub email()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant
Dim stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant
Const EMBED_ATTACHMENT As Long = 1454
' Starts the For Next loop to assign variable email target
For e = 1 To 3
If e = 1 Then vaRecipient = "scott.atkinson@poundland.co.uk"
If e = 2 Then vaRecipient = "neil.maher@poundland.co.uk"
If e = 3 Then vaRecipient = "helen.allsop@poundland.co.uk"
On Error GoTo SendMailError
vaMsg = "THIS AN AUTOMATED EMAIL ----- Please find attached todays Essentials Availability Report, this report includes RZ and Decon data - - - Please review and add action comments to the master file located at I:\H925 Buying\BuyingTeamsALL\New Essentials Availability Report\2008-2009"
stSubject = "*** Essentials Availability Report ***"
stAttachment = "I:\H925 Buying\Scott Atkinson\Projects\Helen's Availability Project\Essentials Availability.xls"
'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
End With
'Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With
Next e
GoTo line1
SendMailError:
Dim Msg
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
'SendMail = False
line1:
End Sub