E-mail macro

[slipperyjim]

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.

 

[daverunt]

Sorted I think?

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

 

[daverunt]

Hmm. posting didn't seem to work.

Try this.

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

 

[daverunt]

oops Page 2!

 

[daverunt]

If you add this to the top it will open Outlook if not already open.

Dim ol As Outlook.Application
Dim olNameSpace As Outlook.Namespace
Dim olInbox As Outlook.MAPIFolder

'Opens Outlook if not already open
Set ol = New Outlook.Application
Set olNameSpace = ol.GetNamespace("MAPI")
'Need this to make Outlook visible as it doesn't have this property like Word or Excel
Set olInbox = olNameSpace.GetDefaultFolder(olFolderInbox)

Email_Subject = "New Hazard report - Risk score greater than 13"

.........etc

 

[slipperyjim]

thanks for your help.

running the code will open outlook without that bit you have added there. but i tried this anyways and i get an error when i try to step through: Compile error: user-defined type not defined.

i will post the code to show you how i pasted in your code.

this issue i had with the code not 'listing' all of the people in the To: box is fixed, however, if there is empty cells the code will add @chhwoodproducts. as a test i entered 2 names in the range, they were listed, but then @chhwoodproducts was entered 21 times into the To: box because all cells from row 19 to 39 were empty.

somehow the code needs to say if the cell is empty either stop the code or don't insert anything in the To: box

Sub Send_Incident_Email_Using_VBA()
Dim ol As Outlook.Application
Dim olNameSpace As Outlook.Namespace
Dim olInbox As Outlook.MAPIFolder
'Opens Outlook if not already open
Set ol = New Outlook.Application
Set olNameSpace = ol.GetNamespace("MAPI")
'Need this to make Outlook visible as it doesn't have this property like Word or Excel
Set olInbox = olNameSpace.GetDefaultFolder(olFolderInbox)
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
        nameList = nameList & "" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
        Email_Send_To = nameList
    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

 

[Michael M]

I think this goes back to my suggestion of having the full address in each cell rather than "adding" the remainder of the address with code.
personally I'd rather be able to change the cells to suit than allowing the code to do it.

But that's just my personal preference

Regards
Michael M

 

[slipperyjim]

you'r right. although the companies emails are the same it doesnt allow for use in other countries where the domain (.com.au) will vary.


in your opinion, how should i change the code then? and will it work as it needs to with empty cells?

 

[Michael M]

I would have thought

If Sheets("Data").Range("O17").Value <> "" Then
        nameList = nameList & "" & Sheets("Data").Range("O" & i).Value
        Email_Send_To = nameList
    End If

would work.
Then in the "O:O" range of cells put the full address of each recipient.
Like I said right at the beginning.....it's way above me, but I'm throwing this stuff to learn as well.

 

[slipperyjim]

hmmmmm, i tried the change in code, when i run the macro it will open a mail message, insert the name correctly and all that, but then i get a system error &H80004005 (-2147467259)

 

[Michael M]

Lets hope someone can come up with a solution to that one.
I can't see how that would happen with the code change though !!