+ Reply to Thread
Results 1 to 4 of 4

Send email to many recipients using Excel...almost

Hybrid View

  1. #1
    Registered User
    Join Date
    05-01-2009
    Location
    Mississippi, USA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Send email to many recipients using Excel...almost

    I feel like I almost have the Excel button I need (thanks to an exhaustive forum search), but I have 2 problems that I need help to correct. First, the code works fine if I have one email address in my Email column, but If I add a second or third email in my column, the code sends the mail, but bounces back because of an Invalid Internet address specified error. The response looks to me like it is taking the first address and appending to the second email recipient. An example would be <example@example.com,example>@example.com.


    Here is the code I am using:

    Sub Lotus()
     
         
        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 EmbedObj As Object
        Dim Subject As String
        Dim Attachment As String
        Dim Recipient As String
        Dim Recip(10) As Variant
        Dim CopyToRecip As String
        Dim BodyText As String
        Dim SaveIt As Boolean
        Dim WasOpen As Integer
        Dim strto As String
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("H:H")
    If cell.Value Like "?*@?*.?*" Then
    strto = strto & cell.Value & ","
    End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
      Dim ccstrto As String
    For Each cell In ThisWorkbook.Sheets("Sheet2").Range("H:H")
    If cell.Value Like "?*@?*.?*" Then
    ccstrto = ccstrto & cell.Value & ","
    End If
    Next cell
    If Len(ccstrto) > 0 Then ccstrto = Left(ccstrto, Len(ccstrto) - 1)
        
        
         
        ' Subject Name
        Subject = "New Basin Group I Correspondence"
         
        'Sending To
        Recipient = strto
        'CC List
        CopyToRecip = ccstrto
         
        SaveIt = True
        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
            WasOpen = 1
        Else
            WasOpen = 0
            Maildb.OPENMAIL
        End If
        Set MailDoc = Maildb.CREATEDOCUMENT
        MailDoc.Form = "Memo"
        MailDoc.sendto = Recipient
        MailDoc.Subject = Subject
        MailDoc.CopyTo = CopyToRecip
        
        ' The text in the memo
        MailDoc.body = "This is a test email from ****** using Excel" & vbNewLine
         
        MailDoc.SAVEMESSAGEONSEND = SaveIt
        If Attachment <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
        MailDoc.PostedDate = Now()
        MailDoc.SEND 0, Recipient
         
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set AttachME = Nothing
        Set EmbedObj = Nothing
        If WasOpen = 1 Then
            Set Session = Nothing
        ElseIf WasOpen = 0 Then
            Session.Close
            Set Session = Nothing
        End If
         
         
        Dim Msg, Style, Title
        Msg = "E-mail has been sent to " & Recipient & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Press OK to continue."
        Style = vbOKOnly + vbInformation
        Title = "Open Issues List"
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    End Sub
    I would also like to view the email before it is Sent. Thanks in advance for the help.
    Last edited by Jimmy0306; 05-12-2009 at 02:29 PM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Send email to many recipients using Excel...almost

    Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

  3. #3
    Registered User
    Join Date
    05-01-2009
    Location
    Mississippi, USA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Send email to many recipients using Excel...almost

    I am almost there, but could someone help me with one final problem...
    The code below creates an email in Lotus based on emails in a column, I would like to be able to edit the Body and Attachments before the email is sent. As the code stands, it automatically sends the email when I click the send Email Button.

    Option Explicit
    Sub Send_Notes_Email_FromList()
      'The subject for the outgoing e-mails.
      Const stSubject As String = "New Basin Group I Correspondence"
      'The message in the bodies of the outgoing e-mails.
      Const vaMsg As Variant = "This message was generated within Microsoft Excel"
      'Variable that holds the list of recipients for each worksheet.
      Dim vaRecipients As Variant
     'Variables for Notes.
      Dim noSession As Object
      Dim noDatabase As Object
      Dim noDocument As Object
      Dim noEmbedObject As Object
      Dim noAttachment As Object
      Dim stAttachment As String
    
      'Variables for Excel.
      Dim wbBook As Workbook
      Dim wsSheet As Worksheet
      Dim lnLastRow As Long
    
      Application.ScreenUpdating = False
      Set wbBook = ThisWorkbook
       'Retrieve the list of recipients.
        With wsSheet
          lnLastRow = Cells(Rows.Count, "A").End(xlUp).Row
          vaRecipients = Range("A1:A" & lnLastRow).Value
        End With
    
        '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 add the attachment.
        Set noDocument = noDatabase.CreateDocument
        Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
        'Add values to the created e-mail main properties.
        With noDocument
          .Form = "Memo"
          .SendTo = vaRecipients
          .Subject = stSubject
          .Body = vaMsg
          .SaveMessageOnSend = True
          .PostedDate = Now()
          .Send 0, vaRecipients
        End With
    
      MsgBox ("The e-mails have successfully been created and distributed."), vbInformation
    
    ExitSub:
    
      'Release objects from memory.
      Set noEmbedObject = Nothing
      Set noAttachment = Nothing
      Set noDocument = Nothing
      Set noDatabase = Nothing
      Set noSession = Nothing
      Exit Sub
    Last edited by Jimmy0306; 05-04-2009 at 07:27 PM.

  4. #4
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Send email to many recipients using Excel...almost

    Jimmy,

    It's been a while since I used Lotus Notes, but I have this snipit of code that might help...

    lnCounter = 0
    'If there are multiple recipients (seperated with comma), an array must be
    ' built of these recipients.  This loop builds the array.
    For lnLooper = 1 To Len(strSendTo)
      If Not Mid(strSendTo, lnLooper, 1) = "," Then
        szTempString2 = szTempString2 + Mid(strSendTo, lnLooper, 1)
        If lnLooper = Len(strSendTo) Then
          ReDim Preserve szTempStringArray(lnCounter)
          szTempStringArray(UBound(szTempStringArray)) = Trim(szTempString2)
          lnCounter = lnCounter + 1
        End If
      Else
        ReDim Preserve szTempStringArray(lnCounter)
        szTempStringArray(UBound(szTempStringArray)) = Trim(szTempString2)
        szTempString2 = ""
        lnCounter = lnCounter + 1
      End If
    Next
    'Add recipient list
    domDocument.SendTo = szTempStringArray
    lnCounter = 0

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1