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.
Bookmarks