1 Reply Latest reply: Jun 25, 2013 10:58 AM by Anne Duffy RSS

    Send image of chart via Lotus notes

      Hi Guys

       

      I have a QV Dashboard, I wish to :

       

      Send a mail to a specific receipient with an image of the dashboard as the body of the mail.

       

      I am currently using a code in Excel that attaches my chart to a mail and sends it via Lotus Notes, Basically I am firstly looking for advice how to best amend this to utilise in QV - and attach it to a button and ---( if that works perhaps make it fire on the value of a chart --- if possible !!!)

       

      Can anyone help please ?

       

      Thanks

       

       

      'The procedure for executing the main task:
      Sub SendWithLotus()
         Dim noSession As Object, noDatabase As Object, noDocument As Object
         Dim obAttachment As Object, EmbedObject As Object
         Dim stSubject As Variant, stAttachment As String
         Dim vaRecipient As Variant, vaMsg As Variant

       

         Const EMBED_ATTACHMENT As Long = 1454
         Const stTitle As String = "Status Active workbook"
         Const stMsg As String = "The active workbook must first be saved " & vbCrLf _
               & "before it can be sent as an attachment."

       

         'Check if the active workbook is saved or not

       

         'If the active workbook has not been saved at all.
         If Len(ActiveWorkbook.Path) = 0 Then
            MsgBox stMsg, vbInformation, stTitle
            Exit Sub
         End If

       

         'If the changes in the active workbook has been saved or not.
         If ActiveWorkbook.Saved = False Then
            If MsgBox("Do you want to save the changes before sending?", _
                  vbYesNo + vbInformation, stTitle) = vbYes Then _
                  ActiveWorkbook.Save
         End If

       

         'Get the name of the recipient from the user.
         Do
            vaRecipient = Application.InputBox( _
                  Prompt:="Please add the name of the recipient such as:" & vbCrLf _
                  & "excel@microsoft.com or just the name if it's internally.", _
                  Title:="Recipient", Type:=2)
         Loop While vaRecipient = ""

       

         'If the user has canceled the operation.
         If vaRecipient = False Then Exit Sub

       

         'Get the message from the user.
         Do
            vaMsg = Application.InputBox( _
                  Prompt:="Please enter the message such as:" & vbCrLf _
                  & "Enclosed please find the Flex Shutdown.", _
                  Title:="Message", Type:=2)
         Loop While vaMsg = ""

       

         'If the user has canceled the operation.
         If vaMsg = False Then Exit Sub

       

         'Add the subject to the outgoing e-mail which also can be retrieved from the users
         'in a similar way as above.
         stSubject = "Flex Shutdown"

       

         'Retrieve the path and filename of the active workbook.
         stAttachment = ActiveWorkbook.FullName

       

         '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

       

         'Release objects from the memory.
         Set EmbedObject = Nothing
         Set obAttachment = Nothing
         Set noDocument = Nothing
         Set noDatabase = Nothing
         Set noSession = Nothing

       

         'Activate Excel for the user.
         AppActivate "Microsoft Excel"

       

         MsgBox "The e-mail has successfully been created and distributed.", vbInformation

       

      End Sub

        • Re: Send image of chart via Lotus notes

          Hi Guys

           

          Its been 2 years and I still havent figured out how to send mails with Qlikview through Lotus Notes - Can anyone assist > I use a version of the following to send mails with Excel through Lotus Notes - can any one helo to make it Qlikview compatable - In QV getting decode on [Set noSession = CreateObject("Notes.NotesSession")]

          Thanks


          A

           

          sub ExcelFile

            strDate = CDate(Date)
            strDay = DatePart("d", strDate)
            strMonth = DatePart("m", strDate)
            strYear = DatePart("yyyy", strDate)
            If strDay < 10 Then
              strDay = "0" & strDay
            End If
            If strMonth < 10 Then
              strMonth = "0" & strMonth
            End If
            GetFormattedDate = strMonth & "-" & strDay & "-" & strYear
            Set v = ActiveDocument.Variables ("EmailAddress")
            Email=v.GetContent.String

          Path = "C:\temp\"
          FileName = "Test_" & GetFormattedDate  & ".xlsx"

          set XLApp = CreateObject("Excel.Application")

          XLApp.Visible = False
          set XLDoc = XLApp.Workbooks.Add

          ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
          XLDoc.Sheets(1).Paste()

          XLDoc.Sheets(1).Columns("A:D").EntireColumn.AutoFit
          XLDoc.Sheets(1).Columns("A:A").ColumnWidth = 15.57
          XLDoc.Sheets(1).Columns("B:B").ColumnWidth = 12.43
          XLDoc.Sheets(1).Columns("C:C").ColumnWidth = 15.29
          XLDoc.Sheets(1).Columns("D:D").ColumnWidth = 15.57

          XLDoc.Sheets(1).Name = "Export"
          XLDoc.SaveAs Path & FileName
          XLApp.Quit

          'Part of mail code
             Dim noSession As Object, noDatabase As Object, noDocument As Object
             Dim obAttachment As Object, EmbedObject As Object
             Dim stSubject As Variant, stAttachment As String
            

          stAttachment = "C:\temp\" & FileName

             'Instantiate the Lotus Notes COM's Objects.
             Set noSession = CreateObject("Notes.NotesSession")
             Set noDatabase = noSession.GETDATABASE("", "")


             '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 = Email
                .Subject = "TESTING ADF"
                '.principal = "Commercial.Team@xxx.ie"
                '.BlindCopyTo = "cops.qualitymanagement@xxx.ie"
                .Body = "Hi Anne"
                .SaveMessageOnSend = True
             End With

             'Send the e-mail.
             With noDocument
                .PostedDate = Now()
                .Send 0, vaRecipient
             End With

             'Release objects from the memory.
             Set EmbedObject = Nothing
             Set obAttachment = Nothing
             Set noDocument = Nothing
             Set noDatabase = Nothing
             Set noSession = Nothing

           

          end sub