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<-

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


---
Regards,
Norman


"Scott Townsend" <(E-Mail Removed)> wrote in message
news:E3678A33-A22D-4F34-B0C1-(E-Mail Removed)...
>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<-

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" <(E-Mail Removed)> wrote in message
news:E3678A33-A22D-4F34-B0C1-(E-Mail Removed)...
>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<-

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

"Norman Jones" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> ---
> Regards,
> Norman
>
>
> "Scott Townsend" <(E-Mail Removed)> wrote in message
> news:E3678A33-A22D-4F34-B0C1-(E-Mail Removed)...
>>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<-
>
>