Insert image into lotus notes mail message using vba

rpwf1

New Member
Joined
Sep 2, 2008
Messages
4
Hi,

I have currently setup some vba code that emails various address with a email message and attachment using lotus notes. I would now like to be able to include a displayed image in the mail message (not as an attachment). Have been searching for a while but can't find anything as yet. Any help would be grately received.

Regards,
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,

thanks for that link think I have a better idea of what I need to do now. From what I understand I have to firstly insert the image into an excel file and then copy the image before pasting it into the body of my email message. I have tried to copy the code tweaking it as required but am unable to integrate it into the current vba code I have. Please see attached code detailing the current method I have for email various addresses with an attachment. What would I need to change in this code to be able to insert an image at the start of the email message.

Code:
Sub exampleSendMail()
'declare varibles (for interaction with Notes)
Dim domSession As New NotesSession
Dim domNotesDbDir As NotesDbDirectory
Dim domNotesDBMailFile As NotesDatabase
Dim domNotesDocumentMemo As NotesDocument
Dim domNotesRichText As NotesRichTextItem
Dim richStyle As NotesRichTextStyle
Dim notesPassword As String
'set locations for mailbox
Const busManMailDBServerName As String = "serverName"
Const busManMailFileName As String = "dbfileName.nsf"
'declare varibles (for choosing attachment file)
Dim latestDateFormat As String
Dim attachmentFileName As String
'declare varibles (for setting up mail message)
Dim recipient(200) As String
Dim ccRecipient(200) As String
Dim bccRecipient(10) As String
Dim txtSubject As String
'declare varibles (for distribution list)
Dim rec As Recordset
Dim Count As Integer
'export report and save locally
[Reporting Code].DBTTowerReport
'start a session to notes
'domSession.Initialize (notesPassword)
domSession.Initialize
'open database
Set domNotesDbDir = domSession.GetDbDirectory(busManMailDBServerName)
Set domNotesDBMailFile = domNotesDbDir.OpenDatabase(busManMailFileName)
 
'create a new memo
Set domNotesDocumentMemo = domNotesDBMailFile.CreateDocument
Set domNotesRichText = domNotesDocumentMemo.CreateRichTextItem("Body")
Set richStyle = domSession.CreateRichTextStyle
 
'open query with eMail distribution list
Set rec = CurrentDb.OpenRecordset("Distribution List")
 
'counter to build recipient array
Count = 0
 
'add eMail addresses from table to array
Do While rec.EOF = False
    recipient(Count) = rec("Email")
    Count = Count + 1
    rec.MoveNext
Loop
'close recordset
rec.Close
Set rec = Nothing
 
'e-mail subject line
txtSubject = "Report to " & Format(latestdate(), "d mmmm")
'set up mail parameters
Call domNotesDocumentMemo.AppendItemValue("Form", "Memo")
Call domNotesDocumentMemo.AppendItemValue("SendTo", recipient)
Call domNotesDocumentMemo.AppendItemValue("CopyTo", ccRecipient)
Call domNotesDocumentMemo.AppendItemValue("BlindCopyTo", bccRecipient)
Call domNotesDocumentMemo.AppendItemValue("Subject", txtSubject)
Call domNotesDocumentMemo.AppendItemValue("Logo", "StdNotesLtr50")
Call domNotesDocumentMemo.AppendItemValue("Principal", "mailbox name")
'attempt to insert image into start of mail message
Dim infoBanner As Object
Dim objXL As Excel.Application
Dim objBook As Excel.Workbook
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Add
Set infoBanner = objBook.ActiveSheet.Pictures.Insert(Application.CurrentProject.Path & "\banner\infoBanner.jpg")
infoBanner.Copy
'I think I understand upto here but and struggling to be able to paste the image into the mail message
Dim test As Object
Set test = domNotesDBMailFile.currentdocument
Call test.gotofield("body")
Call test.Paste
objXL.Application.CutCopyMode = False
Set infoBanner = Nothing
objXL.Quit
Set objBook = Nothing
Set objXL = Nothing
'ok from here
Call domNotesRichText.AddNewLine(2)
Call domNotesRichText.AppendText("Email Text")
Call domNotesRichText.AddNewLine(2)
'format date correctly
latestDateFormat = Format(latestdate(), "yyyy-mm-dd")
'file path and name of attachment
attachmentFileName = Application.CurrentProject.Path & "\Report Exports\Report - " & latestDateFormat & ".xls"
'insert attachment
Call domNotesRichText.EmbedObject(EMBED_ATTACHMENT, "", attachmentFileName, "")
Call domNotesRichText.AddNewLine(2)
'send eMail
domNotesDocumentMemo.Send False
'clear memory
Set domNotesDbDir = Nothing
Set domNotesDBMailFile = Nothing
Set domNotesDocumentMemo = Nothing
Set domNotesRichText = Nothing
Set richStyle = Nothing
End Sub

I would really appreicate any help on this.
 
Upvote 0
Update,

I have noticed that to paste into a document in lotus notes you have to use the line

Code:
dim workspace as object
set workspace = createobject"Notes.NotesUIWorkspace")
I can't seem to be able to use the notesuiworkspace class. I get this error activex can't create object. Does anyone know why this might be or how I can use the NotesUIWorkspace in a different way.
 
Upvote 0
Hi,

isn't there an option to use NotesSession instead of
NotesUIWorkspace?

When using NotesUIWorkspace you can follow the steps in your Lotus Notes client which is not desired. I'm searching for an option where an visible image can be added to automatic created mail via VBA.

Thanks for your ideas.
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top