Posting an Excel Chart into Lotus Notes Email

Question

Ok, just getting into VBA and I have been handed a doosie. I am trying to get a chart, from a worksheet in Excel, placed into Lotus Notes. I have tried both attaching it as a richText format (as you can see from the areas commented out) and as a JPEG (my most recent attempt. The email goes out fine. the body text goes as it is supposed to. I just cannot get the chart into the e-mail body. I could really use some help with this. Here is what I have:

    Sub SendEmail()
  ' setting up various objects
    Dim Maildb As Object
    Dim UserName As String
    Dim MailDbName As String
    Dim MailDoc As Object
    Dim Session As Object
    Dim recipient As String
    Dim ccRecipient As String
    Dim bccRecipient As String
    Dim subject As String
    Dim bodytext As String
    Dim User As String
   ' Dim attachMe As Object
   ' Dim Attachment1 As String
   ' Dim EmbedObj1 As Object
   ' Dim Chart1 As Object
    Dim Fname As String




    User = Application.UserName

     ' setting up all sending recipients
    recipient = "Someoneelse@somewhere.com"
     'ccRecipient =Someoneelse@Somewhereelse.com
     'bccRecipient = ""
    subject = "Week-To-Date GM%"
    bodytext = "" & vbLf & _
               "" & vbLf & _
               "Here is a breakdown of your total GM% for this week.  The graph gives you the GM% by day, with a display at the bottom" & vbLf & _
               " that gives you your WTD Margin Percentage.  We will continue to develop this report for you to provide you with better information."




     ' creating a notes session
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)

    If Maildb.IsOpen <> True Then
        On Error Resume Next
        Maildb.OPENMAIL
    End If

    Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"

    Fname = Environ$("temp") & "\GM%.jpeg"

    Sheets("Chart").Shapes("Chart 2").Chart.ChartArea.Export Filename:=Fname, FilterName:="JPEG"

     ' loading the lotus notes e-mail with the inputed data
    With MailDoc
        .SendTo = recipient
         '.copyto = ccRecipient
         '.blindcopyto = bccRecipient
        .subject = subject
        .Attachment.Add Fname
        .Body = bodytext
    End With


     ' saving message (Change to True if you want to save it)
    MailDoc.SaveMessageOnSend = False

'        Attachment1 = Worksheets("Chart").Shapes("Chart 2").ChartArea
'    If Attachment1 <> "" Then
'        Set attachMe = MailDoc.CREATERICHTEXTITEM("Attachment1")
'        Set EmbedObj1 = attachMe.EmbedObject(1454, "", Attachment1, "Attachment")
'        MailDoc.CREATERICHTEXTITEM ("Attachment")
'    End If

     ' send e-mail
    MailDoc.PostedDate = Now()
     ' if error in attachment or name of recipients


    MailDoc.Send 0, recipient

    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set attachMe = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing

     'Unload Me
    Exit Sub
End Sub

Ken Pespisa · Accepted Answer · 2013-02-01T21:40:37.597

3

This should work just before MailDoc.Send

Dim rtitem As Object
Dim object As Object

Set rtitem = MailDoc.CreateRichTextItem("Attachment1")
Set object = rtitem.EmbedObject ( 1454, "", Fname)

edited Feb 01 '13 at 21:40

answered Feb 01 '13 at 21:02

Ken Pespisa

21,989
3
55
63

I pasted this in and it came back with a User_Defined type not defined error. I'm assuming that I need to un-comment a line or so in there somewhere...any recommendations? – William Feb 01 '13 at 21:15
1

I suppose NotesRichTextItem and NotesEmbeddedObject don't mean anything in VBA. Try using Object instead. – Ken Pespisa Feb 01 '13 at 21:40
Wonderful! That sent the chart as a jpeg attachment! Thanks! – William Feb 01 '13 at 21:49

Posting an Excel Chart into Lotus Notes Email

1 Answers1