98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Emailing a range of people
Emailing a range of people
Resolved · High Priority · Version 2007
Sat 6 Jul 2013, 12:10Delegate Louisa said...
Louisa has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Emailing a range of people
I have googled and googled but no luck.
I am trying to email a list of people (which will change depending on the client).
The plan is to use find to find the row the client is on and this will define the range.
How do I then use the range in the to section of my email the code for which currently looks like this. This the to section you can add as many email addresses as needed.
On Error Resume Next
With OutMail
.To = "louisa.thompson@towertradinggroup.com"
.CC = ""
.BCC = ""
.Subject = strCell2 & " Recap " & strDate
.HTMLBody = RangetoHTML(rng)
.display 'does not send this just produces an email to review
I was thinking I need a for each loop but that's as far as I got.
For each rcell3 in remail
.....
Thanks in advance.
Mon 8 Jul 2013, 09:50
Edited on Mon 8 Jul 2013, 09:50Trainer Gary said...
RE: Emailing a range of people
Hi Louisa
Thanks for getting in touch, good to hear from you.
There's a really good example on a site which I think was brought up during our training:
http://www.rondebruin.nl/win/s1/outlook/bmail7.htm
Which deals with a very similar scenario. I've adapted his code and attached it here.
It runs through every cell in column B and uses that as the email address.
You should be able to adapt the attachment to your data. Give this a try and let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Mon 8 Jul 2013, 10:03Delegate Louisa said...
RE: Emailing a range of people
I had found that example as I usually use Ron de Bruin for emails. But these only have one recipient in each email. I have multi-recipients which are variable and that is the bit I am struggling with. Its important that all recipients are on each email if that makes sense so people can see who has been copied in.
My normal emails have fixed recipients and can do those easily.
Mon 8 Jul 2013, 10:14Delegate Louisa said...
RE: Emailing a range of people
I have had an idea. I would concatenate them to produce a string (with commas in the middle).
Will let you know if that works.
Mon 8 Jul 2013, 10:27
Edited on Mon 8 Jul 2013, 15:21Trainer Gary said...
RE: Emailing a range of people
Hi Louisa
Thanks for clarifying. If you are looking to combine multiple email address in the To: field you need to concatenate them with the semi-colon character (;). With this you could use a simple Do Until loop to go through your range to concatenate them into one string. Something like this:
Dim AllAddresses as String
...
Do Until <condition>
AllAddresses = AllAddresses & ";" & cell.Value
Loop
You could then use
.To = AllAddresses
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Move or Highlight CellsUse any of your movement keys, cursor, Home, End, PgUp or PgDn to highlight cells rows or columns by holding down the Shift key as you move. |
