VBA for smarties Mail Exceldata

0 Access to Outlook

1. Exceldata in emailbody
1.1 File
1.2 Sheet values
1.3 Sheet formulae
1.4 Range values
1.5 Range formulae

2 Exceldata in attachment

2.1 SendMail
2.1.1 File, 1 recipient
2.1.2 File, more
2.1.3 File, address in sheet
2.1.4 File integral
2.1.5 Sheet values
2.1.6 File no VBA
2.1.7 Integral Range
2.1.8 Range values

2.2 Routingslip
2.2.1 File, 1 recipient
2.2.2 File more recipients
2.2.3 File address in sheet
2.2.4 File no VBA
2.2.5 File complete
2.2.6 Sheet no VBA
2.2.7 Integral Range

2.3 Outlook attachment
2.3.1 File
2.3.2 Worksheet
2.3.3 Sheet values
2.3.4 File no VBA
2.3 5 Several sheets
2.3.6 Integral Range
2.3.7 Range values

Mail Exceldata (workbook, worksheet, range)

You can mail Exceldata using several methods:
- sendmail
- routingslip
- Outlook
You can mail the data in the mail or as attachment.
Your choice is dependent of the version of Excel you are working with: the method 'routingslip' isn't available anymore since Excel 2007; in Excel 2000 en 2003 that method is aailable.

In many cases the simple methode 'Sendmail' suffices.
The routingslip offers unique options (sequentially forwarding, monitoring the forwarding, automatically returning changed/amended attachments).
Outlook offers BCC, CC etc.

If you use Outlook you need to know how to refer to Outlook in VBA.
The examples always use the method CreateObject("Outlook.Application")

The examples show how to mail
- an entire workbook
- a worksheet integrally
- only the values in a worksheet
- a certain Range in a worksheet
- only the values in a certain range

Refer to Outlook

VBA has 3 methods to access Outlook from 'outside'; in this case from Excel

I. Outlook is not loaded

the method CreateObject

with CreateObject("Outlook.Application")

x=.GetNamespace("MAPI").GetDefaultFolder(6).Items.count

End With

Restriction:
the method 'CreateObject' doesn't recognize Outlook typenames, only Outlookconstants.
For instance: the typename of folder 'PostIN' in VBA is olFolderInbox; the Outlookcontant of this folder 6.

x=CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items.count

Returns an error, while

x=CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6).Items.count

doesn't.

II. Outlook has been loaded

the method Getobject

With Getobject(,"Outlook.Application")

x=.GetNamespace("MAPI").GetDefaultFolder(6).Items.Count

End With

The method 'GetObject' has the same restrictions regarding typenames as the method 'CreateObject'.

III. Load the Outlook-VBA-library

The method 'references': independent of whether Outlook has been loaded or not.
manually: VBEditor/ MenuBar / Extra / References / Microsoft Outlook 11.0 Object Library /check

' Outlook 2000
' Outlook 2003
' Outlook 2007

sub referentie()

ThisWorkbook.VBProject.References.AddFromFile "msoutl9.olb"
ThisWorkbook.VBProject.References.AddFromFile "msoutl10.olb"
ThisWorkbook.VBProject.References.AddFromFile "msoutl11.olb"

End sub

If the library is loaded you can refer to Outlook as an object in the code.

with Outlook

x=.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items.Count

end with

In this method you can use both Outlook typenames and Outlookconstants.

The examples in this page use the Method 'CreateObject'.

1 Insert Exceldata into the email body

If you want the Exceldata in the email body you have to use Outlook.
You will need HTML code to accomplish this.
So you can convert the complete workbook, a worksheet or any range into an HTML table.
You can also convert all formatting into HTML, but here we stick to the values to keep it simple.

1.1 The complete workbook in the emailbody

Sub volledig_werkboek_in_email()

c01 ="<table border=1 bgcolor=#FFFFF0#>"

For Each sh In Sheets

sn = sh.UsedRange
For j = 1 To UBound(sn)

c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"

Next
c01 = c01 & "</table><P></P><P></P>"

Next

With CreateObject("Outlook.Application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "actual workbook"
.HTMLBody = c01
.Send

End With

End Sub

1.2 The values in 1 worksheet in the emailbody

Sub werkblad_waarden_in_email()

c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").UsedRange
For j = 1 To UBound(sn)

c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"

Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("outlook.application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "actual worksheetvalues"
.HTMLBody = c01
.Send

End With

End Sub

1.3 The formulae in 1 worksheet in the emailbody

Sub werkblad_formules_in_email()

c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").UsedRange.Formula
For j = 1 To UBound(sn)

c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"

Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "worksheetformulae"
.HTMLBody = c01
.Send

End With

End Sub

1.4 Values in a Range in the emailbody

Sub range_waarden_in_email()

c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").Range("A1:K100")
For j = 1 To UBound(sn)

c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"

Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "Range values"
.HTMLBody = c01
.Send

End With

End Sub

1.5 The formulae in a Range in the emailbody

Sub range_formules_in_email()

c0 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").Range("A1:K100").Formula
For j = 1 To UBound(sn)

c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"

Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "Range formulae"
.HTMLBody = c01
.Send

End With

End Sub

2 Exceldata (workbook, worksheet, range) as attachment

- method 'Sendmail'
- method Routingslip
- using Outlook

2.1 SendMail

2.1.1 Workbook to 1 recipient

Sub mail_werkboek_met_sendmail()

ActiveWorkbook.SendMail "snb@forums.nl", "subject"

End Sub

2.1.2 Workbook to several recipients

Sub mail_werkboek_met_sendmail_addressen()

ActiveWorkbook.SendMail Array("snb@forums.eu", "snb@forums.de"), "subject"

End Sub

2.1.3 Workbook to several addresses in a worksheet

- You can add Emailaddresses in a worksheet directly to the argument 'recipients' of the method 'Sendmail'.
Addresses in a column (A1:A10), row (A1:K1) or range (A1:K10) will be converted automatically into a 1-dimensional array.

Sub mail_werkboek_met_sendmail_addressen()

With ActiveWorkbook

.SendMail .sheets(1).Range("A1:G10"), "subject"

End With

End Sub

2.1.4 Interal workbook

Sub mail_werkblad_integraal_met_sendmail()

With ActiveWorkbook

.Sheets(1).Copy

with ActiveWorkbook

.SendMail "snb@forums.nl", "subject"
.Close False

End With

End Sub

2.1.5 Values in a worksheet

Sub mail_werkblad_waarden_met_sendmail()

With ActiveWorkbook

With .Sheets.Add

.Cells = ActiveWorkbook.Sheets(1).Cells.Value
.Copy

with ActiveWorkbook

.SendMail "snb@forums.nl", "subject"
.Close False

End With

.Delete

End With

End Sub

2.1.6 Workbook without VBA

Sub mail_werkblad_waarden_met_sendmail()

With ActiveWorkbook

With .Sheets.Add

ActiveWorkbook.Sheets(1).Cells.Copy .Cells(1)
.Copy

with ActiveWorkbook

.SendMail "snb@forums.nl", "subject"
.Close False

End With

.Delete

End With

End Sub

2.1.7 Integral Range

Sub mail_gebied_integraal_met_sendmail()

With ActiveWorkbook

With .Sheets.Add

ActiveWorkbook.Sheets(1).Range("A1:K25").Copy .Cells(1)
.Copy

with ActiveWorkbook

.SendMail "snb@forums.nl", "subject"
.Close False

End With

.Delete

End With

End Sub

2.1.8 Values in a Range

Sub mail_gebied_waarden_met_sendmail()

With ActiveWorkbook

With .Sheets.Add

.range("A1:K25") = ActiveWorkbook.Sheets(1).Range("A1:K25").Value
.Copy

with ActiveWorkbook

.SendMail "snb@forums.nl", "subject"
.Close False

End With

.Delete

End With

End Sub

2.2 Routingslip

If you are lucky enough to use Excel 2000 or 2003 you can use a routingslip.
Every officedocument can be sent to 1 or several addresses in your standard email programma - not necessarily Outlook-.
Extra facilities in routingslip:
- the option to send the document to all addressees simultaneously (Delivery: xlAllAtOnce), or to send it sequentially to the next recipient in the list after opening/adapting the document (Delivery: xlOneAfterAnother).
- the option to get a reminder if the document has been forwarded to the next recipient in the list (Trackstatus=True)
- the option to return the document after opening/adapting by the recipient (ReturnWhenDone = True)

The recipient will prompted after opening/amending the document:
- to return the amended document to the sender (ReturnWhenDone)
- to forward the document to the next recipient in the list (delivery: xlOneAfterAnother).
the recipient only has to click to do as proposed.
Unfortunately MS deleted this facility in Office 2007.

2.2.1 Workbook to 1 recipient

Sub mail_werkboek_met_circulatielijst()

With ActiveWorkbook

.HasRoutingSlip = True

With .RoutingSlip

.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = True
.TrackStatus = False

End With
.Route

End With

End Sub

2.2.2 Workbook to several recipients

Sub mail_werkboek_met_circulatielijst()

With ActiveWorkbook

.HasRoutingSlip = True

With .RoutingSlip

.Recipients = Array("snb@forums.eu", "snb@forums.de")
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = False

End With
.Route

End With

End Sub

2.2.3 Workbook to recipients in a worksheet

- You can add email addresses directly to the property 'recipients'.
Addresses in a column (A1:A10), row (A1:K1) or range (A1:K10) will be converted automatically into a 1-dimensional array

Sub mail_werkboek_met_circulatielijst()

With ActiveWorkbook

.HasRoutingSlip = True

With .RoutingSlip

.Recipients = sheets(1).range("A1:K10")
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = False

End With
.Route

End With

End Sub

2.2.4 Workbook without VBA

Sub werkboek_zonder_VBA_met_circulatielijst()

With Workbooks.Add

For Each sh In ThisWorkbook.Sheets

With .Sheets.Add

.Name = "c_" & sh.Name
sh.Cells.Copy .Cells(1)

End With

Next

.HasRoutingSlip = True
With .RoutingSlip

.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False

End With
.Route

.Close False

End With

End Sub

2.2.5 Intergal worksheet

Sub mail_werkblad_met_circulatielijst()

With ActiveWorkbook

.sheets(1).copy

With activeworkbook

.HasRoutingSlip = True
With .RoutingSlip

.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False

End With
.Route

End With

End Sub

2.2.6 Worksheet without VBA

Sub mail_werkblad_zonder_VBA_met_circulatielijst()

With ActiveWorkbook

with .Sheets.Add

Activeworkbook.Sheets(1).Cells.Copy .Cells(1)
.Copy
With ActiveWorkbook

.HasRoutingSlip = True
With .RoutingSlip

.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False

End With
.Route
.Close False

End With
.Delete

End With

End Sub

2.2.7 Integral Range

Sub mail_gebied_met_circulatielijst()

With ActiveWorkbook

With .Sheets.Add

Activeworkbook.Sheets(1).Cells(1).CurrentRegion.Copy .Cells(1)
.Copy
With activeworkbook

.HasRoutingSlip = True
With .RoutingSlip

.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False

End With
.Route
.Close False

End With
.Delete

End With

End Sub

2.3 Attachment in Outlook

You can add an attachment to every email in Outlook.
That can only be an Excel workbook; you can't add a worksheet, graph or range.
If you want to attach a worksheet, graph or range you first have to save that into a workbook.

In Excel < 2007 you didn't have to specify the extension nor the 'fileformat' explicitly. Since Excel 2007 it is required.
In the example code we assume the atttachment will get the same extension and the same fileformat as the originating one.
So us the example code only in workbooks that have been saved (with path and extension).

2.3.1 The integral workbook

Sub volledig_werkboek_sturen()

With CreateObject("Outlook.Application").createitem(0)

.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add ThisWorkbook.FullName
.Send

End With

End Sub

2.3.2 An integral worksheet

- copy the worksheet
- save the new, automatically created workbook
- add this new workbook as attachment to the email

Sub enkel_werkblad_integraal_sturen()

Application.DisplayAlerts = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

ThisWorkbook.Sheets("Sheet1").Copy

With ActiveWorkbook

.SaveAs c00, c01
.Close False

End With

With CreateObject("Outlook.Application").createitem(0)

.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send

End With

End Sub

2.3.3 Values in a worksheet

- add a temporary worksheet
- put in this sheet the values of the sheet to send
- copy this temporary sheet so it will become a new workbook
- save this new workbook
- delete the temporary sheet
- add the new saved workbook as attachment to the email

Sub enkel_werkblad_alleen_waarden_sturen()

.Range(ThisWorkbook.Sheets("Blad1").UsedRange.Address) = ThisWorkbook.Sheets("Blad1").UsedRange.Value
.Copy

With ActiveWorkbook

.SaveAs c00, c01
.Close False

End With

.Delete

End With

With CreateObject("Outlook.Application").createitem(0)

.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send

End With

End Sub

2.3.4 Workbook without VBA code

- add a temporary worksheet
- copy all cells of the sheet to be sent
- copy this temporary sheet so it will become a new workbook
- save this new workbook
- deletee the temporary sheet
- add the new workbook as attachment to the eamail

Sub enkel_werkblad_zonder_VBA_sturen()

Application.DisplayAlerts = False

c00 ="E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

With ThisWorkbook.Sheets.Add

ThisWorkbook.Sheets("Sheet1").Cells.Copy .Cells(1)
.Copy

With ActiveWorkbook

.SaveAs c00, c01
.Close False

End With

.Delete

End With

With CreateObject("Outlook.Application").createitem(0)

.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send

End With

End Sub

2.3.5 Several worksheets integrally

- save the file as a copy with a separate name
- open that copy file
- store the sheetnames that shouldn't be sent in an array
- delete the sheets that should not be sent
- save the file
- add this file as attachment to the email

Sub verschillende_werkbladen_integraal_sturen()

Application.DisplayAlerts = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat
sn = Split("weg1|weg2|weg3", "|")

ThisWorkbook.SaveCopyAs c00

With GetObject(c00)

.Sheets(sn).Delete
.Close True

End With

With CreateObject("Outlook.Application").createitem(0)

.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send

End With

End Sub

2.3.6 An integral Range (including formatting, formulae, etc.)

- add a temporary worksheet
- copy te range to be copied to this temporary sheet
- copy the temporary sheet so it becomes a new workbook
- save this new workbook
- delete the temporary sheet
- add the new file as attachment to the email

Sub Range_integraal()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

With ActiveWorkbook.Sheets.Add

ActiveWorkbook.Sheets("Sheet1").Range("A1:AC152").Copy .Range("A1")
.Copy

With ActiveWorkbook

.SaveAs c00, c01
.Close

End With

.Delete

End With

With CreateObject("Outlook.Application").CreateItem(0)

.To = "snb@forums.eu"
.Subject = "example"
.Attachments.Add c00
.Send

End With

End Sub

2.3.7 Values in a Range

- add a temporary worksheet
- put the values of the range to be copied into the range to be sent
- copy the temporary sheet so it becomes a new workbook
- save the new workbook
- delete the temporary worksheet
- add the new file as attachment to the email

Sub Range_alleen_waarden()

.Range(c03) = ActiveWorkbook.Sheets(c02).Range(c03).Value
.Copy

With ActiveWorkbook

.SaveAs c00, c01
.Close

End With

.Delete

End With

With CreateObject("Outlook.Application").createitem(0)

.To = "snb@forums.eu"
.Subject = "example"
.Attachments.Add c00
.Send

End With

End Sub