Email worksheets code - how do you add text to email body?

wallyb

New Member
Joined
Jan 29, 2003
Messages
13
Hi,

I am using the following code to email individual worksheets to separate email addresses:


Code:
Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim strdate As String
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("c9").Value Like "?*@?*.?*" Then
            strdate = Format(Now, "dd-mm-yy h-mm-ss")
            sh.Copy
            Set wb = ActiveWorkbook
            With wb
                .SaveAs "Sheet " & sh.Name & " of " _
                      & ThisWorkbook.Name & " " & strdate & ".xls"
                .SendMail ActiveSheet.Range("c9").Value, _
                          "This is the Subject line"
                .ChangeFileAccess xlReadOnly
                Kill .FullName
                .Close False
            End With
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub


How can I add text to the body of the email.

Thanks
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I'm sorry but you can't actually add text to the email using this method.

The only way you can do that is with different code that actually creates an email.

What that code is depends on which email client you are uising.

Try searching on the board using the name of the email client. eg Outlook
 
Upvote 0
I got this from this board and it's working fine for me.

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Dim strbody As String
For Each cell In ThisWorkbook.Sheets("Sheet1").Range("F1:F20")
strbody = strbody & cell.Value & vbNewLine
Next
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.Subject = "Postia Mp69:stä"
.Body = "Hei " & cell.Offset(0, -1) & ", MP69 jäsen numero " & cell.Offset(0, 2).Value & ", viitenumero " & cell.Offset(0, 3).Value & vbNewLine & vbNewLine & strbody


'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send

End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

It sends F1:F20 as Email body text.

Hope you can use some of it for you.

Have a look in here: http://www.mrexcel.com/board2/viewtopic.php?t=170772

Pekka
:eek: :eek: :eek:
 
Upvote 0
thanks

but that looks like it emails the same body text to a list of emails and you can add an attchment.

I am emailing the separate worksheets as invoices to the person who is suppose to get that worksheet - I just need a way to put a message in the bodyh of the email.
 
Upvote 0
wallyb

The code that Pekka posted is the sort of thing I meant.

It does send an attachment (test.txt) but it also places the values from cells F1:F20 in the body of the email.
 
Upvote 0
This part of the Email-text is individual for every recipient.

.Body = "Hei " & cell.Offset(0, -1) & ", MP69 jäsen numero " & cell.Offset(0, 2).Value & ", viitenumero " & cell.Offset(0, 3).Value & vbNewLine & vbNewLine & strbody

Maybe you get some idea from there.

Pekka
:eek: :eek:
 
Upvote 0
some success

OK

I have had some success but still a long ways off - I have combined an excel html email format to get the body in the email with the "email every single worksheet to a different address" code and it is creating the html email in the body and it is emailing every sheet to the individual email addresses.

Problem is: the sheets that get emailed are still with blank body and I end up with an html email body not addressed on my desktop for each email that goes out.

Can anybody figure out how to combine this code correctly:

Rich (BB code):
Sub Mail_Every_Worksheet()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim strdate As String
    Dim s As String
Dim oOlapp As Object, oOlMail As Object
s = "Hello,
This text will be bold.[/i]

"
s = s & "This text will be italic.
"
s = s & "

<font color=red size=12>See ya!"


    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("c9").Value Like "?*@?*.?*" Then
            strdate = Format(Now, "dd-mm-yy h-mm-ss")
            sh.Copy
            Set wb = ActiveWorkbook
            With wb
                .SaveAs "Sheet " & sh.Name & " of " _
                      & ThisWorkbook.Name & " " & strdate & ".xls"
                .SendMail ActiveSheet.Range("c9").Value, _
                          "Your EFT Network Invoice is Attached"                   
                      




Set oOlapp = CreateObject("Outlook.Application")
Set oOlMail = oOlapp.createitem(0)
oOlMail.htmlbody = s
oOlMail.display


By the way - I use the Click Yes utility and it works great.

Thanks for your help

Regards [/code]
 
Upvote 0
Why are you still using SendMail?
 
Upvote 0
I don't understand your question?

If it refers to the click yes that only gets rid of the security access and automatically clicks yes
 
Upvote 0
In your most recent code you still seem to be using SendMail alongside the code to create an Outlook mail message.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top