none
Changing Lotus Notes VBA code to work with Outlook 2010 RRS feed

  • Question

  •  

    Currently I am working on switching the lotus notes code to a code that will use Outlook instead. The current code I have now reads as follows.  The issue that I need to solve is after access has run these different reports; it attaches them to different emails to different people.  No clue on how to change this code to allow that. Any help is much appreciated! 

    Private Function AutoMail(strRecipient As String, strCC As String, strBCC As String, strPath As String, strFilename As String, strFilename2 As String, strFilename3 As String, subjectText As String, bodyText As String)
    On Error GoTo Err_AutoMail

    Dim strMailFile As String
    Dim strServer As String
    Dim mailDB As Object
    Dim userName As String
    Dim mailDBname As String
    Dim mailDoc As Object
    Dim attachME As Object
    Dim session As Object
    Dim objAttach As Object
    Dim Recip() As String
    Dim CCRecip() As String
    Dim bccRecip() As String
    Dim attachment As String
    Dim saveIt As Boolean
    Dim rtitem As Object
    Dim uidoc As Object

    Set session = CreateObject("Notes.session")
    strServer = ""
    strMailFile = ""
    Set mailDB = session.GetDatabase(strServer, strMailFile)
    If mailDB.IsOpen = True Then
    Else
        mailDB.OPENMAIL
    End If
    'userName = session.userName

        Dim RecipCollection As New Collection

        Recip() = Split(strRecipient, ",")

        For i = 1 To UBound(Recip())
            RecipCollection.Add Recip(i)
        Next
       
        Dim CCRecipCollection As New Collection

        CCRecip() = Split(strCC, ",")

        For i = 1 To UBound(CCRecip())
            CCRecipCollection.Add CCRecip(i)
        Next
       
        Dim BCCRecipCollection As New Collection

        bccRecip() = Split(strBCC, ",")

        For i = 1 To UBound(bccRecip())
            BCCRecipCollection.Add bccRecip(i)
        Next

    'mailDBname = Left$(userName, 1) & Right$(userName, (Len(userName) - InStr(1, userName, " "))) & ".nsf"
    'Set mailDB = session.getdatabase("", mailDBname)
    'If mailDB.isopen = False Then mailDB.openmail
    Set mailDoc = mailDB.CREATEDOCUMENT
    'On Error GoTo UNIDfix
    mailDoc.Form = "Memo"
    mailDoc.SendTo = Recip
    mailDoc.CopyTo = CCRecip
    mailDoc.BlindCopyTo = bccRecip

    mailDoc.Subject = subjectText
    'mailDoc.body = bodyText

    Set rtitem = mailDoc.CREATERICHTEXTITEM("Body")

    If Len(strFilename) > 0 Then
    attachment = "Y:\" & strFilename & ".pdf"
    Call rtitem.EMBEDOBJECT(EMBED_ATTACHMENT, "", attachment)
    'Set attachME = mailDoc.CREATERICHTEXTITEM("attachment")
    'Set objAttach = attachME.EMBEDOBJECT(1454, "", attachment, "attachment")
    'Kill (attachment)  <-- enable this to delete attachment from directory after embedding it in email
    End If

    If Len(strFilename2) > 0 Then
    attachment2 = "Y:\" & strFilename2 & ".pdf"
    Call rtitem.EMBEDOBJECT(EMBED_ATTACHMENT, "", attachment2)
    'Set attachME = mailDoc.CREATERICHTEXTITEM("attachment2")
    'Set objAttach = attachME.EMBEDOBJECT(1454, "", attachment2, "attachment2")
    'Kill (attachment2)  <-- enable this to delete attachment from directory after embedding it in email
    End If

    If Len(strFilename3) > 0 Then
    attachment3 = "Y:\" & strFilename3 & ".pdf"
    Call rtitem.EMBEDOBJECT(EMBED_ATTACHMENT, "", attachment3)
    'Set attachME = mailDoc.CREATERICHTEXTITEM("attachment3")
    'Set objAttach = attachME.EMBEDOBJECT(1454, "", attachment3, "attachment3")
    'Kill (attachment3)  <-- enable this to delete attachment from directory after embedding it in email
    End If

    'GoTo SendEmail  '<-- enable this and disable the next line to autosend the email
    GoTo EditEmail

    EditEmail:
    mailDoc.Save True, False, False
    mailDoc.PutInFolder "Message Center", True
    Dim ws As Object
    Set ws = CreateObject("Notes.session")
    DoEvents
    ws.EDITDOCUMENT True, mailDoc
    Set uidoc = ws.CURRENTDOCUMENT
    Call uidoc.GOTOFIELD("Body")
    Call uidoc.InsertText("Please Take Note:" & vbCrLf & "& vbCrLf & vbCrLf)
    GoTo CleanUp

    SendEmail:
    mailDoc.Save True, False, False
    mailDoc.PutInFolder "Message Center", True
    mailDoc.savemessageonsend = True
    mailDoc.posteddate = Now()
    mailDoc.Send 0, Recip
    GoTo CleanUp

    CleanUp:
    Set mailDB = Nothing
    Set mailDoc = Nothing
    Set attachME = Nothing
    Set session = Nothing
    Set objAttach = Nothing
    Pause (2)
    Exit Function

    Err_AutoMail:
        Select Case Err
            'Case
                'Resume Next
            Case Else
                Call LogError(Err, Error$, "AutoMail()")
        End Select
    Exit Function

    UNIDfix:
    mailDoc.universalID = UNID(32)
    Pause (2)
    Resume

    End Function

    Monday, June 4, 2012 2:54 PM

Answers

  • You will need to rewrite that code using the Outlook Object Model, there is no way around that.

    As I mentioned before, you will need to create new message using Application.CreateItem(0), set the MailItem.To/Subject/Body/etc. properties appropriately, add attachments using MailItem.Attachments.Add.

    http://www.outlookcode.com/ is a great place to start.

    Something along the lines

    set Application = CreateObject("Outlook.Application")
    set Namespace = Application.GetNamespace("MAPI")
    Namespace.Logon
    set oItem = Application.CreateItem(0) 'Create a new message
    oItem.Attachments.Add “c:\test.txt”
    oItem.To = "user@domain.demo"
    oItem.Subject = "Test"
    oItem.Body = "test body"
    SafeItem.Send


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!

    Wednesday, June 6, 2012 3:00 PM

All replies

  • Create new message using Application.CreateItem(0), set the MailItem.To/Subject/Body/etc. properties appropriately, add attachments using MailItem.Attachments.Add.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!

    Monday, June 4, 2012 7:06 PM
  • Can you just change Set session = CreateObject("Notes.session") and Set ws = CreateObject("Notes.session") to "Outlook.Application" and have it work?
    Wednesday, June 6, 2012 2:43 PM
  • There is no chance that will ever work - Notes.Sesssion properties and methods are completely different from those exposed by the Outlook.Application object.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!

    Wednesday, June 6, 2012 2:46 PM
  • Well then where can I find information how to get that code to work for outlook.  Im a intern for my company and they gave me this project to complete.  I have no background in VBA code nor do they.  Also I recieved zero training.

    Wednesday, June 6, 2012 2:53 PM
  • You will need to rewrite that code using the Outlook Object Model, there is no way around that.

    As I mentioned before, you will need to create new message using Application.CreateItem(0), set the MailItem.To/Subject/Body/etc. properties appropriately, add attachments using MailItem.Attachments.Add.

    http://www.outlookcode.com/ is a great place to start.

    Something along the lines

    set Application = CreateObject("Outlook.Application")
    set Namespace = Application.GetNamespace("MAPI")
    Namespace.Logon
    set oItem = Application.CreateItem(0) 'Create a new message
    oItem.Attachments.Add “c:\test.txt”
    oItem.To = "user@domain.demo"
    oItem.Subject = "Test"
    oItem.Body = "test body"
    SafeItem.Send


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.3 is now available!

    Wednesday, June 6, 2012 3:00 PM
  • Thanks for the help lets see if I can figure this out!
    Wednesday, June 6, 2012 3:05 PM
  • Hi Zach- Were you successful in your attempt to transition the code?  I am working on a similar project and I was hoping to learn from your attempt.

    Thanks,

    Jen

    Tuesday, July 16, 2013 3:40 PM