How can I create an Object and send by email?

[Poundland]

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.

[VBA]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[/VBA]

[Kenneth Hobs]

Sendkeys would be the only way that I know to do it for an image of the chart. The ranges are easy to add to the body.

[Poundland]

Sendkeys would be the only way that I know to do it for an image of the chart. The ranges are easy to add to the body.

Ken,

Sorry, can you elaborate more on Sendkeys and how to use them?

[Kenneth Hobs]

I typically do one method or the other. In your case, I think that we can do both. I don't have time to check why this does not work but it should get you started. Review the site that has the parts that I integrated into your code.

LotusNotes should have a Paste method that we can use instead of SendKeys. Sending the keys to get a screen print did not work either. You can do that before the macro.

Be sure to change email address part.

[vba]'http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21829026.html
'http://vbaexpress.com/forum/showthread.php?t=21561
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 e As Integer
Dim ws As Object, uidoc As Object

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 1
If e = 1 Then vaRecipient = "Kenneth Hobson/odot"
'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"
stAttachment = "c:\myfiles\pics\lion.jpg"

'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
'Set objects to edit body
Set ws = CreateObject("Notes.NotesUIWorkspace")
Set uidoc = ws.editdocument(True, noDocument) 'Opens memo window for edit
'Paste clipboard to body
Call uidoc.gotofield("Body") 'Sets focus to body of new memo
SendKeys "^V", True
'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
[/vba]