Email specific cell contents based on email address

T

tharless

New Member
Joined
Feb 28, 2012
Messages
13
Hello,
I have a spread sheet that gets sent to me daily with a list of shop names, client id numbers, and a client endorsment number. The list changes daily, if a client is sent to a shop and has the endorsment number in their file then the information is added to the list. I have a master sheet of email address by shop name, I have used a vlookup to determin the email address of all shops listed on todays list. I have to email each shop a notice that the client has the endorsment. the email needs to include the client Id and endorsment number that is visiting their shop, so again this information is variable with each new list, and can have more than one client visting 1 shop. I have gotten excel to look to a range of cells where my vlookup is located and email every address lissted, but I can not figure out how to populate the body of the email with the client Id and endosment number. I dont mind if the shop recieves a single email for every client they have as long as I do not have to email them manually everyday ;). an example of my spread sheet is
Sheet 1 = list that is sent to me
Cell A Cell B Cell C
<TABLE style="WIDTH: 191pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=254><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 70pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=93>Shop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>ClientId</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 73pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=97>Endorment#</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>TestSubject1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>123</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>12345</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>TestSubject2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>234</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>23456</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>TestSubject3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>345</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>34567</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>TestSubject4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>456</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>45678</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>TestSubject5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>789</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>56789</TD></TR></TBODY></TABLE>
Sheet 2 contains vlookup (also currently after running my vlookup I have to delete any N/A's or I get and error, if anyone knows a way around that that would be great too)
<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=218><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7972" width=218><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 164pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=218>Cell A
testsubject1@email.com


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>testsubject2@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>testsubject3@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>testsubject4@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>testsubject5@email.com</TD></TR></TBODY></TABLE>
and sheet 3 contains my master email list
Cell A Cell B
<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=98>TestSubject1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=64>testsubject1@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>TestSubject2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>testsubject2@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>TestSubject3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>testsubject3@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>TestSubject4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>testsubject4@email.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>TestSubject5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>testsubject5@email.com</TD></TR></TBODY></TABLE>

And the code I have that will send the email currently 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("Sheet2").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
Currently this codes will send everyone an email, but with test as the subject and testing as the body, not concern with subject, it can stay static, but I need the body to contain the client id and endorsment# associated with each individual shop name. In the above example the email TestSubject1 would recieve should read client Id 123 and endorments# 123456, while TestSubject4's email should read client id 456 endorment# 45678.
Sorry if I am over posting or over explaining, just want to be as detailed and thorough as possible. thanks in advance for any help with this. And let me know if you need and more info.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Welcome to the forum,

I use this code to distribute emails, it is checking column K for a yes and uses some columns to make up the body of the message. I am using Outlook and note that you are using Lotus Notes. I hope you can adapt it slightly to work for you.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> EmailRangeList()<br><SPAN style="color:#007F00">'Using Object to create so works with all verisons</SPAN><br><SPAN style="color:#007F00">'Column G holds the email addresses</SPAN><br><SPAN style="color:#007F00">'Column A holds the salutation</SPAN><br><SPAN style="color:#007F00">'Column K holds Yes or No to send</SPAN><br><SPAN style="color:#007F00">'Adjusted code by Trevor G January 2012</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> OutApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> OutMail <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> OutApp = CreateObject("Outlook.Application")<br><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> cleanup<br>    Sheets("sheetname").Activate <SPAN style="color:#007F00">'Change the sheet name here</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Columns("G").Cells.SpecialCells(xlCellTypeConstants)<br>        <SPAN style="color:#00007F">If</SPAN> cell.Value <SPAN style="color:#00007F">Like</SPAN> "?*@?*.?*" And _<br>           LCase(Cells(cell.Row, "K").Value) = "yes" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Checks column K if yes then sends the email</SPAN><br><br>            <SPAN style="color:#00007F">Set</SPAN> OutMail = OutApp.CreateItem(0)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> OutMail<br>                .To = cell.Value<br>                .Subject = Cells(cell.Row, "H").Value<br>                .Body = "Dear " & Cells(cell.Row, "A").Value _<br>                      & vbNewLine & vbNewLine & _<br>                      Cells(cell.Row, "I").Value _<br>                      & vbNewLine & vbNewLine & _<br>                      Cells(cell.Row, "J").Value<br>                      <br>                        <SPAN style="color:#007F00">'"Please find attached an update to my availability " '& '_</SPAN><br>                        <SPAN style="color:#007F00">'"your account up to date"</SPAN><br>                <SPAN style="color:#007F00">'If you need an attachment use the line below</SPAN><br>               <SPAN style="color:#007F00">' .Attachments.Add ("C:\February 2012.doc")</SPAN><br>                .Send  <SPAN style="color:#007F00">'Or use .Display</SPAN><br>                <SPAN style="color:#007F00">'.Display</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">Set</SPAN> OutMail = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> cell<br><br>cleanup:<br>    <SPAN style="color:#00007F">Set</SPAN> OutApp = <SPAN style="color:#00007F">Nothing</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Click to expand...
 
Upvote 0
Thanks for the response Trevor, I am having trouble converting it over to notes thuogh. I think I may have a start but keep getting a type mismatch error where I have changed the text color to red.

Code: 
Sub EmailRangeList()
[COLOR=seagreen]'Using Object to create so works with all verisons[/COLOR]
[COLOR=seagreen]'Column G holds the email addresses[/COLOR]
[COLOR=seagreen]'Column A holds the salutation[/COLOR]
[COLOR=seagreen]'Column K holds Yes or No to send[/COLOR]
[COLOR=seagreen]'Adjusted code by Trevor G January 2012[/COLOR]
    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, vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
    Dim count As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Set noSession = CreateObject("Notes.Notessession")
    Set noDatabase = noSession.GetDataBase("", "")
    [COLOR=seagreen]'If Lotus Notes is not open then open the mail-part of it.[/COLOR]
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    Set noDocument = noDatabase.CreateDocument
 
 
 
    Sheets("Sheet1").Activate 'Change the sheet name here
         For Each cell In Columns("E").cells.SpecialCells(xlCellTypeConstants)
         [COLOR=red]If cell.Value Like "?*@?*.?*" And _[/COLOR]
[COLOR=red]         LCase(cells(cell.Row, "F").Value) = "yes" Then[/COLOR] [COLOR=seagreen]'Checks column K if yes then sends the email[/COLOR]
          With noDocument
                [COLOR=seagreen]'vaRecipient = cell.Value[/COLOR]
                vaSubject = cells(cell.Row, "B").Value
                vaMsg = "Dear " & cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                      cells(cell.Row, "C").Value _
                      & vbNewLine & vbNewLine & _
                      cells(cell.Row, "d").Value
                      End With
 
                       noDocument.SendTo = cell.Value
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
End If
Next cell
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
[COLOR=seagreen]'Activate Excel for the user.[/COLOR]
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub

Any thoughts?
 
Upvote 0
I have made some progress, if in the section of code containing vaRecipient I put only the range with emails for example
vaRecipient= Worksheets("Sheet1").range("A2:A3")
and then in the vaMsg section use the same code
vaMsg= Worksheets("Sheet1").range("D2:D3)
every email address gets every peice of info in the cell range. Anyone know of a way to tie these together so only A2 gets D2 info?
 
Upvote 0
Hi,

Trevors code requires that column K is checked for a string "yes". It is likely you aren't doing it this way so remove that part of the code.

Code: 
 [COLOR=black]If cell.Value Like "?*@?*.?*" And _[/COLOR]
[COLOR=black]        LCase(cells(cell.Row, "F").Value) = "yes" Then 'Checks column K if yes then sends the email[/COLOR]
 
 
[COLOR=black]change to [/COLOR]
 
[COLOR=black]If cell.Value Like "?*@?*.?*" Then[/COLOR]
[COLOR=black]vaRecipient = cell.Value[/COLOR]

The loop for the mails should be after each mail is sent.
You have to set all objects to null before creating the next mail.

So I suspect it should be

Next cell
End Sub
 
Upvote 0
Thanks for the reply dave, that cleared the error, and now I get the end message that my email has been created and sent, however no email is actually sent. I recieved a code somewhere else that I am trying to get to work but am having some trouble, 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. (y)
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
[COLOR=red]vaRecipient = Worksheets("Sheet1").range("A1")[/COLOR]
Do While vaRecipient <> ""
[COLOR=red]vaSubject = "Client ID: " & vaRecipient.Offset(0, 2)[/COLOR]
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
 
Upvote 0
I wish I didn't use Lotus Notes lol thanks for your help, I made the changes and now I get a "Notes error: No name found to send mail to" error which I am assuming is Lotus Notes related. Man if my company would just use outlook I could have solved this with a mail merge in 2 mins lol thanks again for your help. (y)
 
Upvote 0
Sounds like it but before that make sure there's a valid address assigned to the variable vaRecipient by walking the code through using F8.

You could also try sending a mail with a hardcoded address.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
839
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
594
epactheactor
E
E
  • Question
Adding a custom message.
Replies
1
Views
433
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,088
Messages
6,123,057
Members
449,091
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top