Results 1 to 4 of 4

Send email to many recipients using Excel...almost

Threaded View

Jimmy0306 Send email to many recipients... 05-04-2009, 03:04 PM
arthurbr Re: Send email to many... 05-04-2009, 03:20 PM
Jimmy0306 Re: Send email to many... 05-04-2009, 07:24 PM
Chance2 Re: Send email to many... 05-04-2009, 07:36 PM
  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.

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