VBA .SendMail to Multiple Recipients

If one does not want to use CDO to send email, following is the way to send a single email to multiple recipients. However, it appears there is no way to send cc or bcc through .SendMail in VBA.

.SendMail Array("person1@sample.com", "p2@sample.com"), _
"This is the Subject line"

Or

.SendMail Array("person1@sample.com", "p2@sample.com"), _
Subject:="This is the Subject line"

This is particularly useful with work together with ActiveWorkbook in Ms Excel.

For more information, please see:

Ron de Bruin's http://www.rondebruin.nl/mail/tips1.htm

Excel File as Email Attachment for Reporting Services

The scenario is data are pulled out from SQL Server through a rather complicated query without use temporary table, and then copy the dataset to Excel. There are some presentation formats needed in Excel. This setting is the result from requirement since the report readers want it in Excel with live data.

First, using SQL Server Management Studio to arrange SQL Server side work, though it is not necessary. Create a SQL file, organize the SQL query, make sure no temporary table used, and the result should be a single dataset. Complicated formatting as well as query logic should be arranged within a single query by using inner queries as well as WITH AS statement.

If one dimension of original dataset from SQL Server is dynamic, make sure it would be the records dimension (vertical) rather than field dimension (horizontal). This is particularly important because if you have a dynamic horizontal dimension, any change would need human intervention by changing SELECT CASE part in query. You would need to make it as automation as possible. For details how this is work, please refer to http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html. If the dynamic dimension is to be at vertical direction in final report, you can write a special stored procedure to do the transpose transformation, or in Excel, using its built function. By adding CREATE VIEW AS, create a view which sits inside of SQL Server. SQL Server side work has been done.

In Excel, organize the presentation worksheets and dedicate a special worksheet for internal use. All live data including report covering period and report generating date time are to be organized in this internal sheet. Next step is to arrange the automation get external data directly from the view in SQL Server. Click Data in menu, Get External Data, From Other Sources, From Data Connection Wizard, Other/Advanced, A popup window will show. In Provider tab, select SQL Server Native Client 10.0, Next, make a right selection in Select or enter a server name, click Use Windows NT Integrated security, make a right selection in Select the database, then click OK. Finish it. Make sure the dataset from SQL Server will be arrived at right position of that internal worksheet.

Next is to create a button and write a VBA script to complete following tasks:
1. Make a transpose copy of dataset within the internal sheet, if it is necessary.
2. Copy whole or partial dataset to presentation worksheet by using Paste Values. Make sure there is no function in presentation worksheet at all. This does not only show internal works to the end users, but also make readers easily use the data as static.
3. Copy header and footer dynamic data to presentation worksheet by using Paste Values. Make sure this part is also of static data.
4. Hide the internal worksheet. If it is necessary you can also protect the hiden sheet by set password. All you may wish to completely delete the internal worksheet.
5. Automatically send email and attach this Excel file.

VBA Example:

Sub Process()
Sheets("Internal Use").Select
ActiveWorkbook.RefreshAll

Range("A5:AW68").Select
Selection.Copy
Range("B70").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True

Range("D1").Select
Selection.Copy
Sheets("Totals").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

Sheets("Internal Use").Select
Range("A3").Select
ActiveWindow.SelectedSheets.Visible = False

ActiveWorkbook.SendMail
Recipients:="someone@somewhere.com", _
Subject:="Weekly Report "
End Sub

Clearing Obstacles for MS Excel Application Upgrading to 2007

,Recently an Excel application wrote in 2003 version needs to be converted into 2007 following the network infrastructure upgrade. It is noticed there are three parts needed to be attended.

In VBA micro of old application, value of attribute FileFormat for ActiveWorkbook.SaveAs was xlNormal. It is no longer allowed in Excel 2007. The FileFormat now needs to be explicitly expressed. Common values are:

51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)

In this case, value of 52 is used. For more information, please refer to http://www.rondebruin.nl/saveas.htm.

File name is also an issue. This application automatically archives data everyday into a file, of which there is a space in file’s name. Now VBA in Excel 2007 converted the space to ‘%’ sign. Accordingly, the archiving file’s name needs to be changed, as well as the file type changed from .xls to .xlsm.

Originally archiving function involved large chunk of cells being copied from one worksheet to another. After conversion to Excel 2007, it is noticed the file’s size increase drastically without apparent reason. The educated guessing is somehow the application accumulated its clipboard during the automatic archiving process which involved copy-paste and save-as. However, nothing was found either in worksheets or clipboard. Web search shows no such error had been reported to Microsoft. After rewrote codes in this part to remove format as part of clipboard, the problem is resolved, although the reason is still not be found.