Results 1 to 6 of 6

Insert image into lotus notes mail message using vba

This is a discussion on Insert image into lotus notes mail message using vba within the Excel Questions forums, part of the Question Forums category; Hi, I have currently setup some vba code that emails various address with a email message and attachment using lotus ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    4

    Question Insert image into lotus notes mail message using vba

    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,

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,981

    Default Re: Insert image into lotus notes mail message using vba

    Welcome to the Board.

    Look here:

    Inserting Images into Lotus Notes Email

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    4

    Question Re: Insert image into lotus notes mail message using vba

    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.

  4. #4
    New Member
    Join Date
    Sep 2008
    Posts
    4

    Exclamation Re: Insert image into lotus notes mail message using vba

    anyone got any idea's?

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    4

    Default Re: Insert image into lotus notes mail message using vba

    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.

  6. #6
    New Member
    Join Date
    May 2014
    Posts
    2

    Default Re: Insert image into lotus notes mail message using vba

    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.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com