Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Excel 2010 Sending Emails through Lotus Notes to Multiple Recipients

    Hi there,

    I am using Excel 2010 and am trying to improve on a spreadsheet for incoming Courier parcels for my work. At the moment we are able to enter the name of the person and the tracking number etc but have to then go into Lotus Notes to send them an email to notify them about their parcel and I would like to streamline this process. What I have so far is this code from LJM which I have modified a bit:

    Sub CourierPackageEmail()

    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

    '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


    'Add values to the created e-mail main properties.
    With noDocument
    .Form = "Memo"
    .SendTo = "example@example.com"
    .Subject = "Package at Reception"
    .Body = "Hi, There is a package for you at Reception."
    .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


    What I want to be able to do is use the names being put into the spreadsheet to send multiple emails to different addresses using Lotus Notes without having to manually input the email address every time.

    Any comments or suggestions would be greatly appreciated!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You will need to create a loop to go through all the names in the range and substitute it into the ".SendTo = " area

    If you want multiple names, I presume (I don't use Lotus Notes) they would be separated with a semicolon.

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Steve! I'm fairly new to Macro coding so what would a loop look like?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The details will depend on circumstances. You can create a list/maintain the list in VB or in a spreadsheet. You can hard-code the start and end or determine it at runtime. Can you provide details of the situation?
    Steve

  5. #5
    New Lounger
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Courier Package Spreadsheet.png

    So basically as per this image I want to type in the Courier company that delivered it, who entered it into the system, tracking number. When I type the name into the name field I want that name to be inserted in to the "To" part of the email and a generic subject "Package at Reception" as well as a generic message "There is a package at Reception for you" to all come up in an email and be sent to the correct recipient.

Posting Permissions

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