Using Excel To Send E-mails

[Dazzawm]

I have heard you can use excel to send emails. Would it be possible to allocate a number in a cell to an email address then use the data in the cell next to it and add a bit of text that i want run the macro then it sends the e-mail? if it is possible and you need more info please let me know. Thanks.

 

[VoG]

OK, you can hard code the addresses like this

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim LR As Long, i As Long
Dim Addys
Addys = Array("addy1", "addy2", "addy3", "addy4", "addy5", "addy6", "addy7", "addy8", "addy9", "addy10", "addy11")
LR = Range("A" & Rows.Count).End(xlUp).Row


Set OutApp = CreateObject("Outlook.Application")
For i = 1 To LR
    Set OutMail = OutApp.CreateItem(0)
    
    strbody = Range("B" & i).Value & vbNewLine & vbNewLine & "Is in minus?"

    On Error Resume Next
    With OutMail
        .to = Addys(Range("A" & i).Value - 1)
        .CC = ""
        .BCC = ""
        .Subject = "E-mail sent from Excel"
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
Next i
Set OutApp = Nothing
End Sub

 

[Dazzawm]

So I have to put them in order and it assumes the fourth one I put in would be number 4 and so on?

 

[VoG]

Exactly.

 

[Dazzawm]

Thanks just one more thing please. I don't have to have anything in the body of the email just 'data is in minus?' as the subject. Also if I have more than one row with the same number like in my table it will send a separate email for each row?

 

[VoG]

Try this. It will send an e-mail for each row in column A. It will error if the number in column A is less than 1 or greater than the number of items that you assign to the Addys array.

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim LR As Long, i As Long
Dim Addys
Addys = Array("addy1", "addy2", "addy3", "addy4", "addy5", "addy6", "addy7", "addy8", "addy9", "addy10", "addy11")
LR = Range("A" & Rows.Count).End(xlUp).Row


Set OutApp = CreateObject("Outlook.Application")
For i = 1 To LR
    Set OutMail = OutApp.CreateItem(0)
    
    strbody = Range("B" & i).Value
    On Error Resume Next
    With OutMail
        .to = Addys(Range("A" & i).Value - 1)
        .CC = ""
        .BCC = ""
        .Subject = "Is in minus?"
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
Next i
Set OutApp = Nothing
End Sub

 

[Dazzawm]

Thanks for your time VoG. I will try it Monday when I am at work. I keep finding more questions! If I want more than one e-mail address for a number do I just put a comma next to each one?

 

[VoG]

I think that you could do it like this. I cannot test as there is a problem with Virgin Media e-mail

Addys = Array("addy1;anotheraddy", "addy2", "addy3", "addy4", "addy5", "addy6", "addy7", "addy8", "addy9", "addy10", "addy11")

 

[Dazzawm]

I cannot test as there is a problem with Virgin Media e-mail

There always is!!!

 

[Dazzawm]

I have tried this now and it works ok apart from a couple of small things. Firstly it puts whatever is in 'B' in the body of the e-mail, where I need it to be put in the subject with 'Is In Minus' added to it. Secondly when it goes into the e-mail and is ready for sending it does not put my auto-signature into the e-mail (although this is no biggy). And thirdly where the e-mail addresses are long and go onto a second line in the code they turn red and an error appears? Thanks.

 

[VoG]

This should fix it

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim LR As Long, i As Long
Dim Addys
Addys = Array("addy1", "addy2", "addy3", "addy4", "addy5", "addy6", _
"addy7", "addy8", "addy9", "addy10", "addy11")
LR = Range("A" & Rows.Count).End(xlUp).Row


Set OutApp = CreateObject("Outlook.Application")
For i = 1 To LR
    Set OutMail = OutApp.CreateItem(0)
    
    strbody = ""
    On Error Resume Next
    With OutMail
        .to = Addys(Range("A" & i).Value - 1)
        .CC = ""
        .BCC = ""
        .Subject = Range("B" & i).Value & "Is in minus?"
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
Next i
Set OutApp = Nothing
End Sub

Use the _ continuation character to split a line (marked in red).

For the signature see http://www.rondebruin.nl/mail/folder3/signature.htm (probably not worth the effort).