If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Send email, where body equals same row column b as address in column a

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2011
Posts: 14
Send email, where body equals same row column b as address in column a

Hello I need to send a number of emails on a dailey bases, but I need the body of the email to contain items in the cells adjacent to the email address.
example of my spreadsheet: Start in cell A1 of course
Email Shop ClientId Endorsment#
testsubject1@email,com TestSubject1 123 12345
testsubject2@email,com TestSubject2 234 23456
testsubject3@email,com TestSubject3 345 34567
testsubject4@email,com TestSubject4 456 45678
testsubject5@email,com TestSubject5 789 56789
I have a code that allows me to send an email to every address in column A but the subject and body are static and sent to everyone the same. What I need is the email sent to TestSubject1 to include ClientId = 123 and Endorsment# = 12345 and TestSubject2's email to include only Client = 234 and endorsment# = 23456 and so on. The code I have that will send the email is
Code:
Sub send_email()

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, vaSubject As Variant, vaCopyTo As Variant
Dim count As Long
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
Set noDocument = noDatabase.CREATEDOCUMENT
'Get Email address(s)
vaRecipient = Worksheets("Sheet1").Range("A1:A1000")

vaSubject = "TEST"
vaMsg = "Testing"
noDocument.SendTo = vaRecipient

noDocument.CopyTo = vaCopyTo
noDocument.Form = "Memo"
noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Subject = vaSubject
noDocument.Body = vaMsg
noDocument.SAVEMESSAGEONSEND = True

With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
End With

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.

AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: London
Posts: 324
Ok, I've never used Lotus so a lot of your code is alien to me but I assume it works along the same lines as Office. I have a similar setup to you in fact but rather than assign a range of "A1:A1000", I asign the range to a single cell "A1", send the email based on that cell and then loop continually using Range = Range.Offset(1,0) until range<>"".

So, merging my code with your code, I get something like this. If it doesn't work I'm sorry but the method should so you may just need to tweak it.

Note: These are my changes to your Code.
1. Dim vaRecipient As Range
2. Do while Range <> ""
3. vaSubject = vaRecipient.Offset(0,2) 'Move 2 cells to the right of the set range
4. vaMsg = vaRecipient.Offset(0,3) 'Move 3 cells to the right of the set range
5. vaRecipient = vaRecipient.offset(1,0) 'Set the range as 1 row down from the last

Code:
Sub send_email()

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 Range, 
Dim vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
Dim count As Long
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
Set noDocument = noDatabase.CREATEDOCUMENT

'Get Email address
vaRecipient = Worksheets("Sheet1").Range("A1")

Do While vaRecipient <> ""

vaSubject = "Client ID: " & vaRecipient.Offset(0,2)
vaMsg = "Endorsement: " & vaRecipient.Offset(0,3)

noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Form = "Memo"
noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Subject = vaSubject
noDocument.Body = vaMsg
noDocument.SAVEMESSAGEONSEND = True

With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
End With

vaRecipient = vaRecipient.Offset(1,0)

Loop

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.

AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub

Last edited by christyxo; 03-02-12 at 09:12. Reason: Said 'look'. Meant 'Loop'
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2011
Posts: 14
Thank you so much for the response christy, I am having some trouble getting it to work though, I get an "Object variable or With block variable not set" error, at vaRecipient, I changed vaRecient to variant instead of range and that error goes away but then I get an "Object Required" error at vaSubject. you have any thoughts on this? again thanks you so much for your assistance, you guys at this forum are awesome.

Code:
Sub send_email001()

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 range
Dim vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
Dim count As Long

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

Set noDocument = noDatabase.CREATEDOCUMENT

'Get Email address
vaRecipient = Worksheets("Sheet1").range("A1")

Do While vaRecipient <> ""

vaSubject = "Client ID: " & vaRecipient.Offset(0, 2)
vaMsg = "Endorsement: " & vaRecipient.Offset(0, 3)

noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Form = "Memo"
noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.subject = vaSubject
noDocument.body = vaMsg
noDocument.SAVEMESSAGEONSEND = True

With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
End With

vaRecipient = vaRecipient.Offset(1, 0)

Loop

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: London
Posts: 324
I'm sorry - I wrote that all up in notepad last night. I can't test it without lotus either so sorry that you have to do a little tweaking.

What about if you assign Dim vaSubject as String?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: London
Posts: 324
Oh sorry! And I think I know the reason for the initial error. I never setthe range.

Keep the Dim vaRecipient as Range

But change
Code:
vaRecipient = Worksheets("Sheet1").range("A1")
to

Code:
Set vaRecipient = Worksheets("Sheet1").range("A1")
See if that has any impact.

i.e.

Code:
Sub send_email001()

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 range
Dim vaMsg As Variant, vaCopyTo As Variant
Dim vaSubject As String
Dim count As Long

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

Set noDocument = noDatabase.CREATEDOCUMENT

'Get Email address
Set vaRecipient = Worksheets("Sheet1").range("A1")

Do While vaRecipient <> ""

vaSubject = "Client ID: " & vaRecipient.Offset(0, 2)
vaMsg = "Endorsement: " & vaRecipient.Offset(0, 3)

noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Form = "Memo"
noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.subject = vaSubject
noDocument.body = vaMsg
noDocument.SAVEMESSAGEONSEND = True

With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
End With

vaRecipient = vaRecipient.Offset(1, 0)

Loop

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2011
Posts: 14
Almost, now I am getting a "Notes error: No names found to send mail to"
and when I debug it is in the .Send 0, vaRecipient section, I tried changing it to just .Send = vaRecipient, and also just took the line out completly and it goes into running mode and stays there until I get impatient and hit esc. Thank you so much for trying to help me though, I will keep scouring the web until I either fix it or give up lol
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2011
Posts: 14
I found a solution finally, never did get it to work in vba, but if anyone else is having this similar issue GO HERE Even more awesome LotusScript mail merge for Lotus Notes + Microsoft Excel | sacha chua :: living an awesome life
And thank Sacha Chua for her super awesome mail merge script.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On