Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel to Lotus Notes 8.5- Multiple Addressees and References

    Hi All,

    Wonder if you can please help me with the following. Essentially I'm trying to generate a string of emails to users in a spreadsheet. I need a single email to each, along with the corresponding booking ref updated in the Subject. Eg:

    Email Reference
    Person1@company.com ABC
    Person2@company.com DEF
    Person3@company.com GHI

    Each of the above should receive an email with their reference only. I found and adapted (to a very small extent as I'm not very good with this at all) the following at http://windowssecrets.com/forums/sho...es-(Excel-2002) but hope you can please help me update this or let me know where I'm going wrong. I've managed to get it work to a single address with a reference found in B1 but applying the same logic to the email didn't work:

    Sub TestingLotusNotesEmail()
    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
    Dim Btext As String
    Const EMBED_ATTACHMENT As Long = 1454
    'Retrieve the path and filename of the active workbook.
    stAttachment = ActiveWorkbook.FullName
    'Initiate 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")

    'Add values to the created e-mail main properties.
    With noDocument
    .Form = "Memo"
    .SendTo = person1@company.com
    .Subject = "Outstanding references: " & Sheet1.Range("B1")
    .Body = "Please ensure that your SIs are provided before the deadline and that they include the following ***mandatory*** information:"
    .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

    Thanks in advance for your help.

    greymalking

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Welcome to the Lounge as a new poster.

    I haven't tested this but I suspect you just need to use a loop to grab the data you need, something like:

    '...
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

    For i = 1 to 3 '<<adjust as required
    zSendTo = Sheet1.Cells(i, "A")
    zRefs = Sheet1.Cells(i, "B")

    'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")

    'Add values to the created e-mail main properties.
    With noDocument
    .Form = "Memo"
    .SendTo = zSendTo
    .Subject = "Outstanding references: " & zRefs
    .Body = "Please ensure that your SIs are provided before the deadline and that they include the following ***mandatory*** information:"
    .SaveMessageOnSend = True
    End With
    'Send the e-mail.
    With noDocument
    .PostedDate = Now()
    .Send 0, vaRecipient
    End With

    Next i

    'Release objects from the memory.
    '...

    zeddy
    Last edited by zeddy; 2016-04-04 at 12:17.

  3. The Following User Says Thank You to zeddy For This Useful Post:

    greymalkin (2016-04-05)

  4. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for your welcome and for the above, it works perfectly. Really appreciate your help, have a good one...

    greymalkin

Posting Permissions

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