Create email from cells in Excel?

[Scott Townsend]

I have several Worksheets that I want to create emails from.

I want to populate the To: Subject: and body from cells in the spreadsheet.

Whats the best way to do this?

Thank you,
Scott<-

 

[Norman Jones]

Hi Scott,

Ron de Bruin has an extensive set of exanple
procedures for sending emails at:

http://www.rondebruin.nl/sendmail.htm

See for example:

Sub Mail_Sheets_Array()
http://www.rondebruin.nl/mail/folder2/mail3.htm

You could easily replace the hardcoded values for To,
Body, Subject etc with cell referencea or an array of
values

 

[Chip Pearson]

Scott,

A simple method is as follows:


Sub SendMySheet()
Dim Recip As String
Dim Subj As String
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Get recipient out of MySheet!A1
' Get subject out of MySheet!A2
'''''''''''''''''''''''''''''''''''''''''''''''''''
Recip = ThisWorkbook.Worksheets("MySheet").Range("A1").Text
Subj = ThisWorkbook.Worksheets("MySheet").Range("A2").Text
'''''''''''''''''''''''''''''''''''''''''''''''''''
' The following will create a new workbook
' containing only a copy of MySheet. That newly
' created workbook will become the ActiveWorkbook.
'''''''''''''''''''''''''''''''''''''''''''''''''''
ThisWorkbook.Worksheets("MySheet").Copy
'''''''''''''''''''''''''''''''''''''''''''''''''''
' ActiveWorkbook is now the newly created workbook.
'''''''''''''''''''''''''''''''''''''''''''''''''''
ActiveWorkbook.SendMail Recipients:=Recip, Subject:=Subj
ActiveWorkbook.Close savechanges:=False
End Sub

Ron de Bruin is the guru of all things email in Excel. See his site at
http://www.rondebruin.nl/ for much more details and other ways to go about
mailing workbooks.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Scott Townsend]

Thanks!
I ended up with the Following:

Sub SendEmail_Click()
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim Ash As Worksheet

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Ash.Range("A2").Value
.Subject = Ash.Range("A40").Value
.Body = Ash.Range("A43").Value & vbNewLine & _
Ash.Range("A44").Value & vbNewLine & _
Ash.Range("A45").Value & vbNewLine & _
Ash.Range("A46").Value & vbNewLine & _
Ash.Range("A47").Value & vbNewLine & _
Ash.Range("A48").Value & vbNewLine & _
Ash.Range("A49").Value & vbNewLine & _
Ash.Range("A50").Value & vbNewLine & _
Ash.Range("A51").Value & vbNewLine & _
Ash.Range("A52").Value & vbNewLine & _
Ash.Range("A53").Value & vbNewLine & _
Ash.Range("A54").Value & vbNewLine


.Display
'.Send 'Or use Display
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub