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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi TC!

I believe the easiest way to do this is through concatenation. This isn't the most efficient, but starting in cell B2 you can concatenate A1 and A2. Then in B3 concatenate B2 and A3 and autofill that down to A100. Just be sure to add a "; " between the two pieces of text so Outlook recognizes them.
 
Last edited:
Upvote 0
Hi Marmit424

Thanks for responding to my headache

How would I get this to run and open Outlook to generate the email to all these recipients.

Would that still not need some code of sorts or Macro?

Thanks again, I appreciate any reply

Regards

TC
 
Last edited:
Upvote 0
My pleasure TC! It would indeed need a macro to open Outlook and generate the email to recipients. If the email you want to generate uses cell-specific information other than the recipient list, it may be worth the headache of the coding. However, if it's between a saved email, copy/paste recipients vs the code required to email from outlook I would go with the three clicks.
 
Upvote 0
Hi Marmit424

Do you perhaps know what coding I need to put in or what I would need so I can give it a try and compare between the two?
 
Upvote 0
Indeed. The following can vlookup information from Excel using named ranges based on user input (although this could be changed to permanent references) and it can also put that information into a nice HTML table for you. It is definitely a lot more complicated than those three clicks though.

Sub Enter_Title_Here_With_No_Spaces()

'Code: Sets Variables (sVariableName) and Creates Dynamic References (nrVariableName), **Note, this requires you to have specified a named range (in the ex: sVariableName)
Dim sVARIABLENAME As String
Dim nrVARIABLENAME As Range
Set nrVARIABLENAME = Range("sVARIABLENAME")

'Code: Creates Outlook/Email
Dim aOutlook As Outlook.Application
Dim aEmail As Outlook.MailItem
Set aOutlook = New Outlook.Application
Set aEmail = CreateItem(olMailItem)

'Code: Creates Tables
Dim sTABLENAME As Range
Set sTABLENAME = Worksheets("Worksheet Name").Range("A1:Q999")

'Code: Creates input box
Dim sUSERINPUT As Variant
sUSERINPUT = InputBox("Tell the user what information to input into the message box here")

'Code: Sets Variables Equal to HLookup Results
sVARIABLENAME = Excel.Application.WorksheetFunction.HLookup(sUSERINPUT, sTABLENAME, nrVARIABLENAME, False)

'Code: Creates Email
With aEmail
.BodyFormat = olFormatHTML
.display
.To = sVARIABLENAME
.Subject = "Enter Subject Text Here" & sVARIABLENAME
.HTMLBody = "" & "Enter Starting Body Text Here" & sAMBASSADORFIRSTNAME & ",

" & _
"Enter Main Body Text Here " & sVARIABLENAMEHERE (if needed) & "Enter More Main Body Text Here.

" & _
'Code: Creates Table Inside Email
"" & _
"
" & "" & "" & "" & _
"
" & " " & "Enter Text Here That Indicates What Variable Will Be Returned On the Right Column" & "" & sVARIABLENAMEHERE & "

<tbody>
</tbody>
" & _
"
" & "Closing text here" & _
.HTMLBody
End With

End Sub

<tbody>
</tbody>
 
Last edited:
Upvote 0
Thanks very much for this

I unfortunately get an error code when running it.

I have assigned this as a macro to a normal image to run it and it comes up as a user defined error, I hope that makes sense, or am I doing this all wrong?
 
Upvote 0
The error code could be because you do not have a required library enabled as I'm using early binding. You'll need to enable the Microsoft Outlook library. The error code could also be because this code is entirely templatized, meaning it needs your input to determine what table to pull the data from, what variables need to be pulled, named ranges need to be created, etc. It's a fairly large undertaking.
 
Upvote 0
Thanks Marmit424

I think it's a bit too much for my limited understanding of Excel then

I really do appreciate your attempt to help though
 
Upvote 0
It's my pleasure! You can learn it too though. It took me about 45 hours to figure out how to do that but it can be worth it if you're referencing specific cells over and over again and you have to copy/paste each one. It sounds like you don't have that need yet though.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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