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

wallyb

New Member
Joined
Jan 29, 2003
Messages
12
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
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
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
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:
 

wallyb

New Member
Joined
Jan 29, 2003
Messages
12
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
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:
 

wallyb

New Member
Joined
Jan 29, 2003
Messages
12

ADVERTISEMENT

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]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Why are you still using SendMail?
 

wallyb

New Member
Joined
Jan 29, 2003
Messages
12
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
In your most recent code you still seem to be using SendMail alongside the code to create an Outlook mail message.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,670
Members
412,481
Latest member
nhantam
Top