By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,873 Members | 1,014 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,873 IT Pros & Developers. It's quick & easy.

Sending Emails Using a macro

P: 1

I'm trying to send emails using a Macro based on an Excel Sheet and the Email Client I'm using is Lotus Notes.

OS used - Windows Xp.
Language - VB

Below is the Code I'm using for doing the same.

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 EmbedObj1 As Object
Dim recipient As String
Dim ccRecipient As String
Dim bccRecipient As String
Dim subject As String
Dim bodytext As String
Dim Attachment1 As String
Dim t1 As Range

Set t1 = Cells(1, 200)

' setting up all sending recipients
recipient = "" & Addr
ccRecipient = ""
bccRecipient = ""
subject = "" & subj
bodytext = "" & bdy

'// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text
If recipient = vbNullString Or subject = vbNullString Or bodytext = vbNullString Then
MsgBox "Recipient, Subject and or Body Text is NOT SET!", vbCritical + vbInformation
Exit Function
End If

' creating a notes session
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
On Error Resume Next
End If

Set MailDoc = Maildb.CreateDocument
MailDoc.form = "Memo"

' loading the lotus notes e-mail with the inputed data
With MailDoc
.sendto = recipient
.copyto = ccRecipient
.blindcopyto = bccRecipient
.subject = subject
.body = bodytext
End With

' saving message
MailDoc.SaveMessageOnSend = True

Attachment1 = ""
If Attachment1 <> "" Then
Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment1")
Set EmbedObj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment")
End If

' send e-mail !!!!
MailDoc.PostedDate = Now()
' if error in attachment or name of recipients
'On Error GoTo errorhandler1

MailDoc.Send 0, recipient

'Set Maildb = Nothing
'Set MailDoc = Nothing
'Set attachME = Nothing
'Set Session = Nothing
'Set EmbedObj1 = Nothing

'Unload Me
'Exit Sub
' setting up the error message

Set Maildb = Nothing
Set MailDoc = Nothing
Set attachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
' unloading the userform
'Unload Me
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)

But the problem is that when the Variable Addr contains more than one email addresses separated by a comma Lotus is unable to send emails and bounces the mail back saying "Error transferring to; Invalid Internet address specified" .... I've tried separating the email addresses with the greater than and less than sign at the beginning and end of emails ("<email>") but that way Lotus does not give me any errors but neither does the email reach the recipient ... I'm trying to find a solution to this issue ... if any one has any idea please let me know via email or through this forum.
Sep 19 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.