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]

Take a look here http://www.rondebruin.nl/sendmail.htm

 

[Dazzawm]

Thanks I have read that and it is a bit complex for me to understand. I think a lot of changes will be have to be made to meet my requirements.

1) I will have numbers 1-11 each with an allocated e-mail address, say column A.

2) Next to that number in column B will be a number or text.

3) The macro will have to look at that number in A to find the e-mail address, then look at the data in 'B' and send that to the e-mail address with a comment along the lines of 'is in minus?' on the end of it.

I am using both Excel and Outlook 2007.

 

[VoG]

Try 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
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 = Range("A" & i).Value
        .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]

Where do I put the choice of the 11 e-mail adresses so it looks at the number and decides it needs to be sent there?

 

[Dazzawm]

</B>

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 293px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Both these to one addy</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">DATA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center">This to another</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center">This to another</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center">This to another</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center">This to same addy as row 4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">DATA</TD><TD style="TEXT-ALIGN: center">This to another</TD></TR></TBODY></TABLE>

There will be several rows like above, so I think the various addies will have to be stored in the macro.

 

[VoG]

Set up your sheet like this

Excel Workbook
	A	B
1	addy1	data1
2	addy2	data2
3	addy3	data3
Sheet1

 

[Dazzawm]

I cant do it like that as I get a different spreadsheet everyday with those numbers in the column and like I say maybe with several rows. Unless I can run a macro that will replace the numbers with the e-mail adresses first.

 

[VoG]

The simplest thing would be to create a lookup table of numbers and corresponding addresses then use VLOOKUP to bring them into the sheet.

 

[Dazzawm]

Not to worry I think having to do that everyday will take me longer than what I do now.