Email from Excel Headache

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Why would you want to concatenate the recipient list?

Once your email is created (this line
Code:
Set aEmail = CreateItem(olMailItem)
)

You can add recipients individually; to use your example of taking address from "Sheet 1" within a range A1:A100. This short code creates a new message and populates the "to list"

Code:
Sub CreateMail()

Dim WS As Worksheet, x As Long
Dim ou As Outlook.Application
Dim nEm As Outlook.MailItem
Set ou = New Outlook.Application
Set nEm = CreateItem(olMailItem)
Set WS = ThisWorkbook.Sheets("Sheet1")

For x = 1 To 100
    nEm.Recipients.Add (WS.Range("A" & x).Value)
Next

nEm.Display

End Sub
You also noted errors (correctly identified by being caused as outlook library access). If you cannot establish a link to that library you can also use "late binding" and that link is no longer required.

See this VBA references and early binding vs late binding | Excel Matters for more information
 
Last edited:

TC-Fireman

Board Regular
Joined
Oct 14, 2009
Messages
79
Hi ScottR

I used your code but it did not work

I have just email addresses from A1 - A100

Then I added an email icon into the spreadsheet, then assigned your macro to that icon.

When I click the email icon I get an error in return.

Am I doing this completely wrong?

I am not a real newbie, but it seems I am doing something wrong here, yet I have done this before with no problems.

Could you perhaps dumb it down for me to understand :biggrin:
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Hi

Did you resolve your library issue? If not then please try this version which is converted to late binding. Other things that can cause failure here is

1) Any blank cells in A1 - A100
2) Sheet name is not actually "Sheet 1"

Could you try and look at those three things and if not resolved post the details of your error message and the failure point?

Code:
Sub CreateMail()

Dim WS As Worksheet, x As Long
Dim ou As object
Dim nEm As object
Set ou = CreateObject("Outlook.Application")
Set nEm = ou.CreateItem(olMailItem)
Set WS = ThisWorkbook.Sheets("Sheet1")

For x = 1 To 100
    nEm.Recipients.Add (WS.Range("A" & x).Value)
Next

nEm.Display

End Sub
 

TC-Fireman

Board Regular
Joined
Oct 14, 2009
Messages
79
Hi ScottR

I have no idea how to resolve the library issue, if there is one, that is way out of my scope

However, there are blanks between A1 - A100 cells, and the error coming up from your latest code is "Automation Error" when I click on the email Icon

I appreciate you guys thinking I know a bit more than I do, but unfortunately the library issue is beyond my comprehension, sorry

Thanks for the feedback, hopefully we are getting close to resolving this
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Library references are easy. Under VBA its "Tools" then references. Just ensure the correct tick box is ticked; that is not required with late binding though so ignore for now.

This version ignores blank cells

Sub CreateMail()

Dim WS As Worksheet, x As Long
Dim ou As object
Dim nEm As object
Set ou = CreateObject("Outlook.Application")
Set nEm = ou.CreateItem(olMailItem)
Set WS = ThisWorkbook.Sheets("Sheet1")

For x = 1 To 100
if WS.Range("A" & x).value<>"" then nEm.Recipients.Add (WS.Range("A" & x).Value)
Next

nEm.Display

End Sub
 

TC-Fireman

Board Regular
Joined
Oct 14, 2009
Messages
79
Hi ScottR

This work like an absolute Bomb !!!!!

Thank you so very much, wow, does this save time

One last question, if I decide to change the cell reference once I finalized this spreadsheet, where would I change the coding

For instance if I rename "Sheet1" to CRM and change the cell reference to C6 - C100 instead of A1-A100, I changed the below code, but it still pulls through A1-A100

Sub CreateMail()
Dim WS As Worksheet, x As Long
Dim ou As Object
Dim nEm As Object
Set ou = CreateObject("Outlook.Application")
Set nEm = ou.CreateItem(olMailItem)
Set WS = ThisWorkbook.Sheets("CRM")
For x = 6 To 100
If WS.Range("C" & x).Value <> "" Then nEm.Recipients.Add (WS.Range("C" & x).Value)
Next
nEm.Display
End Sub

Please can you advise where I went wrong here

Thanks again, this works a treat, but I may just need to tweak it after I am done with this spreadsheet as displayed here
 
Last edited:

TC-Fireman

Board Regular
Joined
Oct 14, 2009
Messages
79
Hi ScottR

Don't worry, I stupidly got the cell reference wrong, it was H instead of C

Thank you very much indeed, I really can't thank you enough

All the best bud

Regards

TC
 

Watch MrExcel Video

Forum statistics

Threads
1,095,800
Messages
5,446,554
Members
405,406
Latest member
tuxy

This Week's Hot Topics

Top