E-mail macro

S

slipperyjim

Board Regular
Joined
May 2, 2009
Messages
142
Hi,
I have a simple e-mail macro that works, however, someone that understands VBA would need to enter the email addresses if they are to change, which it is likely they will.

is it possible to set up this macro so that when clicked it will email out to a list of peole in a cell range?

the cell range: Sheet "Data", Range U17:U39

in these cells are the email addresses typed like this:
firstname.lastname @ business.com.au


not all of the cells in the range will be populated.

if possible could it be set up to just type in the front of the email address as the back is always the same e.g

firstname.lastname (macro code will add the @business.com.au bit)

thanks for any help offered.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
this is the code i am using, how can i change the Email sen to section to look through the range O17 to )39 and if there is a name in any of the cells, send off an email.



Code: 
Sub Send_Incident_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
        Email_Subject = "New Hazard report - Risk score greater than 13"
        Email_Send_From = ""
        Email_Send_To = Sheets("Data").Range("O17").Value & "@chhwoodproducts.com.au"
        Email_Cc = ""
        Email_Bcc = ""
        Email_Body = "A new report has been entered into switchboard with a risk score of 13 or above recorded, please review as soon as possible." & Chr(13) & Chr(13) & "Thank you," & Chr(13) & Chr(13) & "SwitchBot: automated messenger."
        Set Mail_Object = CreateObject("Outlook.Application")
        Set Mail_Single = Mail_Object.CreateItem(o)
        With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .Display
            .send
   
End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
End Sub
 
Upvote 0
Hi Jim
This is way beyond my skills, but couldn't you use
Code: 
Sheets("Data").Range("O2:17").Value

and put the entire e mail address in each cell, rather than .Value& "@chhwoodproducts.com

Regards
Michael M
 
Upvote 0
no that didn't work. but i thank you for your input. using the full email could be doable, just less data entry for people i guess.
 
Upvote 0
Hi Jim,

Enter the below code next to email send to

Code: 
Email_Send_To = Sheets("Data").Range("O17").Value & "@chhwoodproducts.com.au"
For i = 18 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
        Email_Send_To = "," & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    End If
Next

The above condition works if you are sure that there will be some value in O17. If you are not sure whether there will be some values in O17 then use the below code

Code: 
For i = 17 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
        Email_Send_To = "," & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    End If
Next

replace line 7 in your code "Email_Send_To = Sheets("Data").Range("O17").Value & "@chhwoodproducts.com" with the above code.

Hope this will work!

Cheers,
Dine
 
Upvote 0
Dine,
thank you very much for your help. stepping through this code works fine, however when it gets to the end i get the following error:

Run-time error '2147467259 (80004005)':
Automation error
Unspecific error


here is the code i used (i went with your second option)
Code: 
Sub Send_Incident_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
        Email_Subject = "New Hazard report - Risk score greater than 13"
        Email_Send_From = ""
        
        For i = 17 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
        Email_Send_To = "," & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    End If
Next
        Email_Cc = ""
        Email_Bcc = ""
        Email_Body = "A new report has been entered into switchboard with a risk score of 13 or above recorded, please review as soon as possible." & Chr(13) & Chr(13) & "Thank you," & Chr(13) & Chr(13) & "SwitchBot: automated messenger."
        Set Mail_Object = CreateObject("Outlook.Application")
        Set Mail_Single = Mail_Object.CreateItem(o)
        With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .Display
            .send
   
End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
End Sub

what are your thoughts, did i paste it in there incorrectly?
 
Upvote 0
while stepping through the code i watched the locals view window. when it hits the send to line it will see the first value in row 17 correctly, but when it runs through to the next row it will see the next value but not add it to the email list.

basically it just runs through the code and will send a email whoever's name is in row 39, if it is blank it returns an error.
 
Upvote 0
What happens if you remove the If statement
Code: 
If Sheets("Data").Range("O17").Value <> "" Then

It seems to have to do that statement every time it follows the NEXT command.

Regards
Michael M
 
Upvote 0
it does the same thing, just selects the value of cell 39 and puts the last part of the email in the To: list

still doesnt add all people in the range to the To: list
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Send e-mails selected from a list in excel
Replies
1
Views
204
Sequoyah
Sequoyah
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
215
mumps
mumps
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
276
Micron
M
K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
446
Domenic
D
S
  • Solved
Run-Time Error '438' when populating e-mail addresses from within Excel VBA
Replies
1
Views
293
rollis13
rollis13

Forum statistics

Threads
1,224,045
Messages
6,176,055
Members
452,701
Latest member
rfhandel

Share this page

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