Email from Excel Headache

TC-Fireman

Board Regular
Joined
Oct 14, 2009
Messages
79
Hi guys

First off, Merry Christmas, Happy Holidays to everyone

I have searched Google flat, and also this forum, and I still cannot find a very basic solution to my problem.

If I have email addresses in the range from A1:A100, what is the easiest was to "One Click" either a Macro or hyperlink to open up a new email and populate all the email addresses in my range above, as recipients.

I may also have to insert this macro in "Sheet 3", but pull the information from A1:A100 in "Sheet One"

Please can anyone assist, I have looked everywhere, but I just find very confusing and long VBA code.

Any help would be hugely appreciated

Regards

TC
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,451
Members
448,573
Latest member
BEDE

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