Excel Macro to email to addresses in "Contacts" Tab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Workbook that gets updated periodically and when updated it need to
be emailed to a list of individuals. The list may change from time to time so
the email addresses for a given Workbook are listed on a separate tab, in
column A.

I want a macro to loop through the list, and add each address to the TO
field of the email. I have the following code which has two problems:
1. It creates a separate email for each address, and I want one email with
everyone on it.
2. The loop is looping through 20 rows, but the number of addresses may vary
from 1 or 2 to more than 20. What I want is for the macro to loop until it
hits a blank row and then stop. So users do not have to maintain the macro,
just the contacts tab.

Here's the code I have:
Sub EmailToContacts()
Dim olApp As Object, olMsg As Object, wb As Workbook, c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Set olApp = CreateObject("Outlook.Application")
For Each c In Sheets("Contacts").Range("A1:A20")
Set olMsg = olApp.CreateItem(0)
With olMsg
.To = c.Value
.Subject = "This is a test" 'change as desired
.Body = "A Macro in Excel sent this using Emails in a tab
[Contacts]" 'change as desired
.Attachments.Add ThisWorkbook.FullName 'workbook must be saved
first
.Display 'change to .Send if you don't want displayed,
Redemption will be needed though
End With
Next c
Application.EnableEvents = True
Application.ScreenUpdating = True
Set olApp = Nothing
Set olMsg = Nothing
End Sub
 
You can use

If you want to mail to all E-mail addresses in column C use this code
instead of .To = "(e-mail address removed)"

Dim cell As Range
Dim strto As String
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

See also
http://www.rondebruin.nl/mail/tips2.htm
 
This worked great. Thanks for your help.

Is there an easy way to copy a Macro from one Excel File to another?

Appelq
 
We have a bunch of spreadhseets that I want to put the Macro in.
Example: 1 spreadhseet holding customer contract / proposal info for each
large account. There may be 50 -100 spreadsheets.

It would make things a lot easier, be able to open a spreadsheet and
"Import" a macro from another file.

thanks,
Appelq
 
Copy the macro in your personal.xls
This is a hidden workbook that Excel opens when you start the program.

To create the personal.xls record a dummy macro and in "Store macro in" choose personal macro workbook.

Replace the dummy macro with your macro and you can use it in every workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl



Appelq said:
We have a bunch of spreadhseets that I want to put the Macro in.
Example: 1 spreadhseet holding customer contract / proposal info for each
large account. There may be 50 -100 spreadsheets.

It would make things a lot easier, be able to open a spreadsheet and
"Import" a macro from another file.

thanks,
Appelq
Click to expand...
 
What do I do to send hyperlinks in the email I need to send out.
Ron said:
Copy the macro in your personal.xls
This is a hidden workbook that Excel opens when you start the program.

To create the personal.xls record a dummy macro and in "Store macro in" choose personal macro workbook.

Replace the dummy macro with your macro and you can use it in every workbook
Click to expand...
 
Back
Top