Examples for Exporting to EXCEL Workbook Files

Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

Write Data From a Recordset into an EXCEL Worksheet using EXCEL's CopyFromRecordset (VBA)

Browse to a single EXCEL File and Export Data to that EXCEL File via TransferSpreadsheet (VBA)

Browse to a single Folder and Export Data to a New EXCEL File in that Folder via TransferSpreadsheet (VBA)

Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Generic code to generate "on the fly" a query that uses one or more controls on an open form as parameters, and then export that query to an EXCEL file. This example concatenates the parameter values into the generated SQL statement and then saves the query so that it can be exported. The query then is deleted after the export is completed.

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery " & _
"WHERE NameOfTableOrQuery.FieldName >= " & _
Format(Forms!NameOfForm!ADateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & _
" And NameOfTableOrQuery.FieldName <=" & _
Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & "';"

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)

Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate EXCEL files, one file for each manager.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)

Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate worksheets within the same EXCEL file, one worksheet for each manager.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
' filename without the .xls extension
' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)
Const strFileName As String = "PutEXCELFileNameHereWithoutdotxls"

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file must already exist, and the worksheet must already exist in the EXCEL file), and then to loop through the recordset and write each field's value into a cell in the worksheet, with each record being written into a separate row in the worksheet. The starting cell for the EXCEL worksheet is specified in the code; after that, the data are written into contiguous cells and rows. This code example uses "late binding" for the EXCEL automation.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("WorksheetName")

' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go

Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

rst.MoveFirst

If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If

' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop

End If

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Write Data From a Recordset into an EXCEL Worksheet using EXCEL's CopyFromRecordset (VBA)

Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file does not already exist), and then to use EXCEL's CopyFromRecordset method to copy the data from the recordset into the first worksheet in that EXCEL file, with each record being written into a separate row in the worksheet. The code allows for a header row to be created in the worksheet if this is desired. This code example uses "late binding" for the EXCEL automation.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strPathFileName As String, strWorksheetName As String
Dim strRecordsetDataSource As String
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

' Replace C:\Filename.xls with the actual path and filename
' that will be used to save the new EXCEL file into which you
' will write the data
strPathFileName = "C:\Filename.xls"

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
strRecordsetDataSource = "QueryOrTableName"

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Create a new EXCEL workbook
Set xlw = xlx.Workbooks.Add

' Rename the first worksheet in the EXCEL file to be the first 31
' characters of the string in the strRecordsetDataSource variable
Set xls = xlw.Worksheets(1)
xls.Name = Trim(Left(strRecordsetDataSource, 31))

' Replace A1 with the cell reference of the first cell into which the
' headers will be written (blnHeaderRow = True), or into which the data
' values will be written (blnHeaderRow = False)
Set xlc = xls.Range("A1")

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then
' Write the header row to worksheet
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If

' copy the recordset's data to worksheet
xlc.CopyFromRecordset rst
End If

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

' Save and close the EXCEL file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.SaveAs strPathFileName
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Browse to a single EXCEL File and Export Data to that EXCEL File via TransferSpreadsheet (VBA)

Generic code to browse to a single EXCEL file, and then to export the data to that EXCEL file. This generic method uses the Windows API to browse to a single file the code for this API (which was written by Ken Getz) is located at The ACCESS Web ( www.mvps.org/access ).

First step is to paste all the Getz code (from http://www.mvps.org/access/api/api0001.htm ) into a new, regular module in your database. Be sure to give the module a unique name (i.e., it cannot have the same name as any other module, any other function, or any other subroutine in the database). Then use this generic code to allow the user to select the EXCEL file to which the data are to be exported.

Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the EXCEL file:"

' Change C:\MyFolder\ to the path for the folder where the Browse
' window is to start (the initial directory). If you want to start in
' ACCESS' default folder, delete C:\MyFolder\ from the code line,
' leaving an empty string as the value being set as the initial
' directory
strInitialDirectory = "C:\MyFolder\"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")

strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)

If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table from which
' the data are to be exported
strTable = "tablename"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Browse to a single Folder and Export Data to a New EXCEL File in that Folder via TransferSpreadsheet (VBA)

Generic code to browse to a single folder, and then to import the data from the first (or only) worksheet in all EXCEL files that are located within that folder. All of the EXCEL files' worksheets must have the data in the same layout and format. This generic method uses the Windows API to browse to a single folder; the code for this API (which was written by Terry Kreft) is located at The ACCESS Web ( www.mvps.org/access ).

First step is to paste all the Kreft code (from http://www.mvps.org/access/api/api0002.htm ) into a new, regular module in your database. Be sure to give the module a unique name (i.e., it cannot have the same name as any other module, any other function, or any other subroutine in the database). Then use this generic code to allow the user to select the folder in which the EXCEL files are located.

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the new EXCEL file will be created:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace filename.xls with the real name of the EXCEL file
' that is to be created and into which the data are to be
' exported
strFile = "filename.xls"

' Replace tablename with the real name of the table from which
' the data are to be exported
strTable = "tablename"

strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

After I read many posts in the newgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing to figure out what actually works and what doesn't work when using this argument (NOTE that the use of the Range argument for exports is an UNDOCUMENTED feature in ACCESS).

Here are the results of my tests for your information and entertainment. My tests were done with ACCESS 2003 and EXCEL 2003.

EXCEL FILE DOES NOT ALREADY EXIST
---------------------------------------------------------------

If the EXCEL file will be created by TransferSpreadsheet, the Range argument
can be used to create a range in the new file that describes the cells that
contain the exported data on the worksheet. This Range argument also is used
to name the worksheet onto which the exported data are written. This
overrides the normal operation of TransferSpreadsheet, which is to name the
worksheet using the name of the table or query being exported. For example,
this action:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "MyName"

will create the file "C:\Test.xls" and the data will be written onto the
first worksheet, which will be named MyName (the Worksheet.Name property,
not the Worksheet.CodeName property); and the cells into which the data are
written will be a Range named MyName in the new file. This range will
include the field names that are exported as the first row of data, and the
range will begin in cell A1.



EXCEL FILE ALREADY EXISTS
------------------------------------------------

The Range argument can be used to identify the actual Range into which the
exported data are written. TransferSpreadsheet ignores worksheet names when
looking for the Range in the workbook file. It looks specifically for a
defined Range of cells.

However, it is in this situation where I believe many posters have run into
problems with this undocumented feature.

1) If the Range exists (cell range, that is) AND if that range encompasses
more than a single cell (at least two cells), the data are exported to that
range of cells. If the number of records and/or fields are more or fewer
than the "size" of the range (number of rows and columns), the data are
correctly exported and the Range is redefined to match the size of the
exported data in terms of width and depth of the range (number of rows and
number of columns). Note that any formatting in the cells within this range
is retained (e.g., Bold, Highlight color, font color, etc.).


2) If the Range does not exist in the workbook file, TransferSpreadsheet
creates a new worksheet, names it with the Range argument value, writes the
data onto that worksheet, and creates a new Range (also named with the Range
argument value) to define the cells that contain the exported data. If a
worksheet with the same name as what is in the Range argument already exists
in the workbook file, the new worksheet that is created is named using
standard EXCEL process, namely, the Range argument name followed by a 1.
Thus, if I use MyName as the Range argument and export to an existing file,
I can get one of the following results:

a) File already contains a worksheet named MyName but does not
contain a Range named MyName: A new worksheet named MyName1 is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.

b) File does not contain a worksheet named MyName and does not
contain a Range named MyName: A new worksheet named MyName is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.


3) If the Range exists (cell range, that is) AND if the Range consists of
a single cell (e.g., A1), then strange things happen -- note that it doesn't
matter if the Range starts in cell A1 or not. And because of these strange
things, this is where the feature is unusable for exporting. I haven't
defined exact "rules" to describe what happens (although it appears that how
far the range is moved appears to be "the original row number plus 93"
columns (if the Range was originally in column A), but here are my
observations in this situation (I won't guarantee that you won't see
different behaviors):

a) If the worksheet name is the same name as the Range name, and
the Range begins in cell A1, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell A1 (with the field names row) -- BUT the existing
range is moved to cell CQ1 (94 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.

b) If the worksheet name is the same name as the Range name, and
the Range begins in cell A5, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell E5 (with the field names row) -- BUT the existing
range is moved to cell CU5 (98 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.

c) If the worksheet name is not the same as the Range name, and
the Range begins in cell A1, the exported data are written to a new
worksheet that is named the same as the Range argument value, and the
existing Range is then moved to cell IV1 (the last column in the sheet) on
that new worksheet, and there is no Range created for the cells that contain
the exported data.

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

Posting Levels in Dynamics GP – A Review

I have been getting questions from many of my clients and network on the various levels of postings to the General Ledger accounts that are available in Dynamics GP. Hence I decided to provide some information on the same to all the folks in this community.

The level of posting to the GL accounts is determined from couple of setups working hand in hand in Dynamics GP which I will elaborate below. The initial setup is to define the level of postings for various accounts in the Account Maintenance window from Cards >> Financials >> Accounts as illustrated below.

We can specify the levels of posting from the various series into the General Ledger module. The various options available for posting levels are

• Detail
• Summary

Once this has been setup, we need to define the level of posting for various transactions in various modules in the Posting Setup window from Microsoft Dynamics GP >> Tools >> Setup >> Posting >> Posting as illustrated below.

So for the purpose of this case study, I created a couple of receivable invoices with the distributions explained below and saved them into a batch called RECVINV.

Invoice #1

Invoice Number	Account Number	Type	Debit (DR)	Credit (CR)
INV0001	000-1200-00	RECV	$500	-
INV0001	000-4100-00	SALES	-	$500

Invoice #2

Invoice Number	Account Number	Type	Debit (DR)	Credit (CR)
INV0001	000-1200-00	RECV	$700	-
INV0001	000-4100-00	SALES	-	$700

And I have setup the account 000-1200-00 to post at a Summary Level in the Sales Series in the Account Maintenance window, whereas I have setup the account 000-4100-00 to post in detail in the Sales Series in the Account Maintenance window.

Now, in the Posting Setup window, if we select the option to create one journal entry per transaction, posting will always be done at a detailed level (irrespective of the setting specified in the Account Maintenance window) (i.e.) There will be a one-to-one match between the distribution lines in the journal entry and the distribution that we had noted in the transaction posted in the sub ledgers.

So when the above batch RECVINV is posted in the Receivables module, there will be 2 journal entries created with the distributions explained below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$500	-
12345	000-4100-00	-	$500

Journal Entry #2

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12346	000-1200-00	$700	-
12346	000-4100-00	-	$700

However in the above window, if we specify the option to create a journal entry per batch, we have two levels of roll ups that are available when journal entries are created when the sub ledger transactions are posted.

If the “Use Account Settings” option is unchecked, then when a batch of transactions is posted from the sub ledger module, the system creates one journal entry for all transactions posted in the sub ledger batch. However, there is no roll-up done at the account level, even though the accounts have been setup to post at summary level in the Account Maintenance window.

So in the same case study example above, if the batch was posted in the Receivables module, there will be one journal entry created with the distributions illustrated below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$500	-
12345	000-4100-00	-	$500
12345	000-1200-00	$700	-
12345	000-4100-00	-	$700

If the “Use Account Settings” option is checked, then when a batch of transactions is posted from the sub ledger module, the system creates one journal entry for all transactions posted in the sub ledger batch and the distribution amounts are rolled based on the posting levels for the accounts that are defined in the Account Maintenance window.

So in the same case study example above, if the batch was posted in the Receivables module, there will be one journal entry created with the distributions illustrated below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$1200	-
12345	000-4100-00	-	$500
12345	000-4100-00	-	$700

Note: Keep in mind that the various levels of postings will also be in effect only when we perform a batch posting in the sub ledger. If the posting is done at a transaction level, the system will always post in detail to the General Ledger (irrespective if the settings in the Account Maintenance window and the Posting Setup window for the specific sub ledger transaction).

Hope this article provides some insight into the General Ledger posting levels that are available in Dynamics GP.

http://cvakumar.com/msdynamics/2010/05/16/posting-levels-in-dynamics-gp-a-review/

Maintaining Data Integrity between Sub Ledgers and General Ledger

I have been involved in the process of reconciliation between the sub ledgers and the general ledger at various clients and there has been various scenarios in which there has been a break between the sub ledgers and the general ledger balances. A few key scenarios are quoted below

1. A transaction posted in the sub ledgers do not have a corresponding transaction in the general ledger.
2. A transaction posted in the sub ledger is backed out/corrected at the general ledger level.
3. Manual posting to the sub ledger control accounts in the General Ledger.

In this article, I am going to provide some tips to avoid any of the above situations and ensure that there is data integrity between the sub ledgers and the general ledger. This will ensure that the periodic audits done in the system proceeds in a smooth manner to a great extent.

The first two errors listed above can be prevented by disabling the following options in the General Ledger setup window from Microsoft Dynamics GP >> Tools >> Setup >> Financials >> General Ledger.

By unmarking the option “Deletion of Saved Transactions”, a journal entry batch that is created when a sub ledger transaction is posted, cannot be deleted at the General Ledger level. This prevents any deletion of a journal entry that was created from a sub ledger transaction. Note that if we have enabled “Post Through General Ledger” in the posting setup, this would not make a big difference, since whenever a transaction is posted in the sub ledger, the corresponding general ledger transactions get automatically posted as well.

By unmarking the option “Voiding/Correcting of Subsidiary Transactions”, the system will not allow us to void a journal entry batch that is created when a sub ledger transaction is posted. This prevents any voiding of a journal entry that was created from a sub ledger transaction. that if we have enabled “Post Through General Ledger” in the posting setup, this would not make a big difference, since whenever a transaction is posted in the sub ledger, the corresponding general ledger transactions get automatically posted as well. Further, this option will not allow the user to back-out (or) back-out and correct a journal entry that was posted for a sub ledger transaction.

The third error listed above can be eliminated by disallowing account entry for all the sub ledger control accounts like the GL accounts for Accounts Receivable, Accounts Payable and Inventory accounts, in the Account Maintenance window from Cards >> Financial >> Accounts, as shown below. We need to unmark the option “Allow Account Entry” for all the control accounts needed.

Unmarking this option for the key control accounts, will prevent the user from picking up this account in any transaction entry windows and will prompt the message as shown below.

This option will prevent any direct posting into the sub ledger control accounts in the General Ledger. All postings will happen only from the sub ledger module.

These options mentioned above are some key setups that we can enable in GP to minimize the data integrity issues between the sub ledgers and the general ledger which consumes precious reconciliation time which is spent by many resources during the key audit time.

http://cvakumar.com/msdynamics/2010/05/16/maintaining-data-integrity-between-sub-ledgers-and-general-ledger/

How to Hammer Out Deal Terms Like Warren Buffett

In the early stages of selling your business, you're better off taking cues from the Oracle of Omaha and keeping the lawyers away from the negotiations.

I once went to a Broadway play starring James Belushi. I had good seats, so Belushi was only a few feet in front of me. I remember how captivating it was to be that close to someone famous. I became engrossed in the show and felt as though Belushi was talking directly to me. I lost touch with time and my surroundings. I forgot about all of the work behind the scenes—the lighting, staging, music—and just sat there and enjoyed Belushi’s performance.

Selling your business should work like a good play: You should put your best, most engaging actors onstage and keep the people handling the messier details of the deal behind the curtain. In my opinion, the lawyers should remain offstage until you agree to a set of business terms with the acquirer.

It's tempting to let your lawyer be the “bad cop” in the early stages of negotiations, pounding on the table, demanding better terms. However, coming to an agreement on the core business terms before the lawyers get involved will ensure a positive foundation that will serve you well when the legal teams inevitably reach loggerheads on some of the finer details.

One of the biggest hurdles in coming to an agreement on the core business terms is drafting a simple letter that outlines the deal in language both parties understand. It's hard not to get mired in the details and document every conceivable possibility. If you get bogged down, take inspiration from Warren Buffett.

Buffett is the master of writing about business in plain English. When Buffett tries to buy a company, he approaches the CEO with a short, plain-spoken letter before any of the deal-makers get involved. Read Buffett’s annual letter to Berkshire Hathaway shareholders if you find your correspondence with a potential acquirer becoming too technical.

Here are a few things to work out with an acquirer (in plain English) before you get the lawyers involved:

• The cash on closing (what you get the day the deal closes)

• The earn-out (or vendor take-back) you are agreeing to, along with a clear understanding of what budget and resources you have at your disposal to meet the goals you’re signing up for

• What happens if you decide to leave or the buyer asks you to leave before the earn-out or transition period is over

• The working capital in the business at closing (how much money needs to be left in the company the day it changes hands)

• Your salary and benefits when you become an employee of the acquirer

• The degree of decision-making autonomy you’ll have as an employee during the earn-out or transition period (e.g., Do you get to decide what technology platform to use? Do you control decisions around marketing and selling? How about hiring and firing?)

If you can agree to a set of business terms, you’ll be building a good working relationship with your acquirer, which will be important once you become a division of their business. Besides, if you can’t come to an agreement on the basic business terms with a buyer, no two lawyers will be able to agree to the legal terms, which are infinitely more complex.

So follow Buffett’s lead and get the basics down in plain English before you invite the lawyers onto the stage.

http://www.inc.com/articles/2010/04/negotiating-basic-deal-terms.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+inc%2Fheadlines+%28Inc.com+Headlines%29&utm_content=My+Yahoo

5 Ways to Ensure Mediocrity in Your Organization

The recession is no excuse for ignoring, misusing, or demeaning talent. But hey, if that's what you really want to do, follow these suggestions.

The last time I checked, the U.S. led the world in productivity per employee. That's the good news. The bad news is that much, if not all, of that boost in productivity has come on the backs of workers, especially salaried types viewed by too many management teams as infinitely elastic resources. As one management consultant told me: "The average company takes better care of its copiers than it does its talent."

Many chief executives use the tough competitive environment as a handy excuse to put off salary increases, tighten the screws on performance, and generally drop any pretense of creating a human-centered workplace. But the tough-economy picture has two sides. Only those companies that make the effort to keep their employees productive by treating them decently can expect to see continued productivity gains. Much of the workforce has tuned out, waiting for a more welcoming job market to make career moves. Those organizations that haven't wavered on their commitments to flexibility, recognition of talent, and transparent leadership will keep A-list players on board as the job market improves. Their competitors may be wishing they'd paid a little more attention to employee TLC as employees start peeling off for greener pastures.

Here are five of the most insulting leadership practices, the ones that virtually guarantee a business will end up with the most self-esteem challenged, optionless team members when the dust settles.

1. If you desire a mediocre workforce, make sure your employees know you don't trust them.

Nothing spells "You're dirt to us" like a corporate culture that screams, "We don't trust you as far as we can throw you." I refer to company policies that require employees to clock in and out for lunch or software that tracks every keystroke and change of URL in case a molecule of nonwork-related activity squeaks into the workday. When employees know they're not trusted, they become experts at "presenteeism"—the physical appearance of working, without anything getting done. Congratulations! Your inability to trust the very people you've selected to join your team has cost you their energy, goodwill, and great ideas.

2. If you want to drive talented people away, don't tell them when they shine.

Fear of a high-self-esteem employee is prevalent among average-grade corporate leadership teams. Look how hard it is for so many managers to say, "Hey Bob, you did a great job today." Maybe it's a fear that the bit of praise will be met with a request for a pay raise. Maybe it's the fear that acknowledging performance will somehow make the manager look weak. Whatever the reason for silence, leaders who can't say, "Thanks—good going!" can plan on bidding farewell to their most able team members in short order.

3. If you prefer a team of C-list players, keep employees in the dark.

Sharp knowledge workers want to know what's going on in their organizations, beyond their departmental silos. They want some visibility into the company's plans and their own career mobility. Leaders who can't stand to shine a light on their firms' goals, strategies, and systems are all but guaranteed to spend a lot of money running ads on Monster.com. Marketable top performers want a seat at the table and won't stand for being left in the dark without the information they need to do their jobs well.

4. If you value docility over ingenuity, shout it from the rooftops.

I heard from a new MBA who had joined a global manufacturer. "They told me during my first week that I need a manager's signature to organize a meeting," he recalled. "They said I'm too low-level to call a meeting on my own, because unauthorized meetings of nonmanagers are against company policy." How fearful of its employees would a leadership team have to be to forbid people to gather together to solve problems? The most desirable value creators won't stick around to be treated like children. They'll hop a bus to the first employer who tells them, "We're hiring you for your talent—now go do something brilliant."

5. If you fear an empowered workforce more than you fear the competition, squash any sign of individualism.

When you go to college, you learn about Economic Man, but in the corporate workplace we see that real people don't always act rationally. Lots of individual managers and plenty of leadership teams fear nothing more than the idea that a self-directed employee might buck authority. That's equivalent to shaking the organizational power structure to its foundation, possibly a fate worse than death. Leaders who want the most docile, sheep-like employees more than the smartest and ablest ones create systems to keep the C players on board and drive the A team out the door. They do it by instituting reams of pointless rules, upbraiding people for miniscule infractions ("What? Twenty minutes late? Sure you worked here until midnight last night, but starting time is starting time.") and generally replacing trust with fear throughout their organizations. Companies that operate in fear mode will never deliver great products and services to the marketplace. Their efforts will be hamstrung by their talent-repelling management practices.

How long will it take these enterprises to figure out they're shooting themselves in the foot? It doesn't matter—you'll be long gone by then.

http://finance.yahoo.com/career-work/article/109519/5-ways-to-ensure-mediocrity-in-your-organization?mod=career-leadership

SQL Server APPLY operator

If you would love two scoops of yummy SQL Server query goodness, the APPLY operator is for you.

As I've said before, rarely a day goes by when I don't say "D'oh!" in surprise (and embarrassment) at being unaware of a tool or technology that I probably should have known about, but just didn't.

The SQL Server APPLY operator is one of those gems that I should have known about 5 years ago. I really need to start buying those "What's New In SQL Server" books, and actually reading them.

Imagine this Microsoft Dynamics GP scenario: You need to query vendors and include the vendor's PRIMARY address information, as well as their REMIT TO address information. But it's not quite that simple (it never is).

You find that the client has inconsistent vendor address IDs in their database. Sometimes they just use the address ID of "REMIT", sometimes "REMIT TO" and sometimes "REMIT ADDRESS", among others. And naturally, there are some vendors who don't have a separate REMIT TO address at all. In theory, some vendors may have two or more remit addresses, so you have to handle that situation as well--in which case you only want the TOP 1 remit address record.

While trying to figure out how to write such a query, I came across this article that discussed the OUTER APPLY operator.

Think of it as an OUTER JOIN, but without needing the ON keyword and subsequent matching columns from the two tables. It's also a bit like a subquery, but with the flexibility of a JOIN.

In short, it will make your inner SQL geek salivate.

Here is an example query that retrieves basic vendor information, along with the TOP 1 primary and remit to address records, allowing me to reference the fields in my query through a derived table.

Note that I used the OUTER APPLY version, since I want to return the vendor information even if there is no primary or remit to address. If you only want to return records that have a match in your apply operation, you can use CROSS APPLY, which works like an INNER JOIN. (hence the two scoops of yummy goodness)


SELECT
RTRIM(v.VENDORID) AS VENDORID,
RTRIM(v.VENDNAME) AS VENDNAME,
RTRIM(ISNULL(vpa.VNDCNTCT, '')) AS CONTACT,
RTRIM(ISNULL(vpa.ADDRESS1, '')) AS ADDRESS1,
RTRIM(ISNULL(vpa.ADDRESS2, '')) AS ADDRESS2,
RTRIM(ISNULL(vpa.CITY, '')) AS CITY,
RTRIM(ISNULL(vpa.STATE, '')) AS STATE,
RTRIM(ISNULL(vpa.ZIPCODE, '')) AS ZIPCODE,
RTRIM(ISNULL(vra.ADDRESS1, '')) AS REMITADDRESS1,
RTRIM(ISNULL(vra.ADDRESS2, '')) AS REMITADDRESS2,
RTRIM(ISNULL(vra.CITY, '')) AS REMITCITY,
RTRIM(ISNULL(vra.STATE, '')) AS REMITSTATE,
RTRIM(ISNULL(vra.ZIPCODE, '')) AS REMITZIPCODE,
RTRIM(ISNULL(vpa.PHNUMBR1, '')) AS PHONE1,
RTRIM(ISNULL(vpa.PHNUMBR2, '')) AS PHONE2,
RTRIM(ISNULL(vpa.FAXNUMBR, '')) AS FAX
FROM PM00200 v
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%PRIMARY%') AS vpa
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%REMIT%') AS vra


I told you it was tasty! Bon Appétit!


Here are a few more articles on the topic:

http://msdn.microsoft.com/en-us/library/ms175156.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://decipherinfosys.wordpress.com/2007/10/08/apply-operator-in-sql-server-2005/

FROM: http://dynamicsgpland.blogspot.com/2010/05/sql-server-apply-operator.html

What financial data business owners neeed to know everyday

Yesterday Sales
Yesterday Cashflow
Last 30 Day Sales
Last 30 Day Cash Flow
Last 30 Day Top Selling Items
Last 365 Day Sales
Last 365 Day Cash Flow
Low Inventory Items

The Dashboard Demystified

by Victoria Hetherington, Dundas Data Visualization
Wednesday, September 23, 2009

This article aims to provide a fundamental understanding of what a dashboard is and provide a brief look at what dashboards can do for an organization. There is an abundance of dashboard literature out there and this article is a good starting point for those interested in a high level understanding of dashboards.

A dashboard is a business tool that displays a set of PIs (performance indicators), KPIs (key performance indicators), and any other relevant information to a business user. Dashboard data is often displayed in real-time after retrieval from one or more data sources in a business. Dashboards are interactive, allowing an executive to drill into particular aspects of the display or switch between facets or views of the data. Key performance indicators need special consideration because they are high-level measurements of how well an organization is doing in achieving critical success factors – in other words, the goals or targets set by an organization in their strategic planⁱ. Dashboards are composed of data visualization tools like charts, grids, gauges and maps. Many different sectors of many different businesses benefit from dashboards: both a miner determining where to drill from a map of a geographical area and a CEO deciding where to channel funds would benefit from dashboard use.

Dashboards can provide an effective solution to the overwhelming amount of data that business users experience every dayⁱⁱ. A dashboard can save employees time - and companies money - by making everything more intuitive, easier to observe, and allowing for extensive, real-time access instead of going through papers and emails to compile information. In order to have a significant return on the investment of a dashboard, it is important that the dashboard be exactly tailored to suit the needs of a company or a particular role within a company. In addition, it is important that a dashboard have metrics that are meaningful and useful to its target audience. Dashboard vendors, and those looking to invest in dashboards, must consider dashboard options such as interface – i.e., would a primarily graphical interface or an integration of graphics and text suit best? How about a static display or an interactive displayⁱⁱⁱ? Would it be necessary to invest in an ODS (operational data store) to store and support access to data and metadata^iv?

Before deciding on a dashboard and becoming familiar with dashboard categorization and the countless types of data visualization tools available, it is important to be aware of several traits all good dashboards have in common. All dashboards should display a quantitative analysis of what is going on with immediacy and intuitiveness. They should offer creative visual insight, such as an anatomy chart or heat map would offer a hospital – but the interface must not be overly complex: distractions, clichés, and unnecessary embellishments can create confusion^v. Good dashboards offer appropriate context for data: for example, a gauge may show that Company X sold 1000 units this year, but compared to what? Highlighting relevant data, effective color use, and a visually appealing interface all help too. A good business dashboard, in other words, pairs dashboard technology with visual effectiveness. In more explicit terms, here are some key elements of a good dashboard:

• It communicates with clarity; quickly, and compellingly. Simplicity is key.
• It has minimal unnecessary distractions, no matter how interesting, which could cause confusion.
• It organizes business information to support meaning and usability
• It applies the latest understanding of human visual perception to the visual presentation of information
• It is pleasant to look at

Due to the incredible array of available dashboard technologies, definitive categorization of dashboards is a difficult task. One can categorize dashboards in terms of role, or strategic, analytic, and operational dashboards. Vendors are probably most used to referring to dashboards in these terms^vi; though there are several other categorizations that are very common as well.

Most often, dashboards are used for strategic purposes^vii. The common executive dashboard, designed for a strategic manager of a medium-sized business, for example, is a strategic dashboard. The strategic dashboard allows for a quick overview of an organization’s ‘health,’ so to speak; assisting with executive decisions such as the formation of long-term goals. The strategic dashboard, therefore, doesn’t require real-time data: what is going on right now is not important, what is pressing is what has been going on. When designing a strategic dashboard, visual communications experts recommend keeping the interface simple – showing just what has been going. It should be noted that not only higher-up corporate people use strategic dashboards to monitor an organization. For example, a middle manager can monitor data on a dashboard, and then create a presentation to pitch to his CEO about the data he has observed.

The analytic dashboard, as the name suggests, assists with data analysis. This can include making comparisons, reviewing extensive histories, and evaluating performance. When using an analytic dashboard, a tactical manager can go beyond what is going on – as with the strategic dashboard – and drill into the causes. They can determine why sales targets were not met; why problems keep occurring^viii. Through exploring these patterns, goals can be set to correct these issues over time.

The operational dashboard monitors functions which need constant, real-time, minute-by-minute attention, from a blood pressure monitor in an operating room to an assembly line in a refrigerator factory. As with the strategic dashboard, it is recommended that an operational dashboard have a simple interface: no statistics or analyzing. All that is required of a good operational dashboard is immediacy and practicality, like names of workers and sections of the workplace. They are generally used by those on a departmental, rather than executive level^ix.

Categorization of dashboards can go another, equally common route: a vendor could categorize dashboards by the type of data they handle: either quantitative; or pertaining to data based on quantity or number – which is overwhelmingly more common – or qualitative; which could include scheduling and simple, pertinent lists. A vendor could also think of dashboards in terms of domains, both vertical and lateral. A vertical dashboard is specialized for a specific industry, such as mining, manufacturing, banking, or healthcare. Dashboards in lateral domains are designed for the internal departments that most organizations have: the financial, marketing, manufacturing, and human resources departments of a bank, a mining company, and a hospital could all use a similar dashboard to create goals and determine solutions for financial problems; likely from strategic and analytic dashboards for example. It is hard to have an understanding of one mode of categorization without the other.

There are many, many different kinds of dashboards, all tailored to fit specific roles and almost every lateral and vertical cross-section of the world’s industries. This article described three fundamental types of dashboards, but a dashboard does not need to fit one of the categories in order to be successful. Successful dashboards convey a great deal of dense necessary information with clarity and immediacy. Over time, a successful dashboard will improve an organization’s decision-making based on aggregated BI, assist in goal-setting, help monitor negative trends, and improve workplace productivity.

http://www.dashboardinsight.com/articles/digital-dashboards/fundamentals/the-dashboard-demystified.aspx?newsletterid=042010

Formatting Dynamics GP Phone Numbers in a SQL Query

I'm sure this has been addressed thousands of times over the years, but since I just had to write the script, I thought I would post it here for posterity.

By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.

I recently had to write a SQL query that would be used to export vendor information to a CSV file. Because the system receiving this data has a free form phone number field, we wanted to make sure that the phone number data coming from GP was formatted consistently.

I'm sure there are several other ways to do this, but this is the script that I created to put the phone numbers in US phone format. It's pretty basic, but gets the job done.


SELECT
CASE RTRIM(PHNUMBR1)
WHEN '00000000000000' THEN ''
WHEN '' THEN ''
ELSE '(' + SUBSTRING(PHNUMBR1, 1, 3) + ') ' + SUBSTRING(PHNUMBR1, 4, 3) + '-' + SUBSTRING(PHNUMBR1, 7, 4)
+ CASE WHEN RIGHT(RTRIM(PHNUMBR1), 4) <> '0000' THEN ' x' + RIGHT(RTRIM(PHNUMBR1), 4) ELSE '' END
END AS PHONE
FROM PM00200


If you are having to store international phone numbers in GP, then it probably makes sense to edit the "Format" resource in Modifier to use a Fill of Space, and no format string.

Or if anyone has any other tips or tricks on querying and formatting GP phone numbers, post a comment!

VBScript To Send Email Using CDO

Sending email with CDO is a simple task. First we create a reference to the CDO component

Set objMessage = CreateObject("CDO.Message")
then fill-in Sender, Subject and Recipient (To) fields of the headers and the body text which can be either plain text or HTML. You can also add a file attachment. You then use the Send method to send the email.

Below I'll show all three types of emails, and how to send an email using a remote SMTP server in the event you are not running your own. I've added and example to illustrate how to request a return receipt and delivery status notifications.

Please note, when using the AddAttachment method in your scripts you must use a fully qualified pathname as the argument to the method. Using just a file name or a relative path will produce the error The specified protocol is unknown.

If you receive an error message related to objMessage.From then you should try replacing it with objMessage.Sender

I've added sample code to illustrate how to load the body of the email from a text file on your disk.

I've added a sample of how to load recipient data from a database.

I've added a sample illustrating how to use data from Excel in an email.

If you are looking for an ASP based email form processor then please look here.

If you are interested in a mass mailer using CDO and VBScript, look here.

---

This sample sends a simple text email that can be viewed in any email client. Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = "me@my.com" objMessage.To = "test@paulsadowski.com" objMessage.TextBody = "This is some sample message text." objMessage.Send Sending an HTML email. Note the use of the Cc & Bcc properties to send using Blind Carbon Copy (Bcc) and Carbon Copy (Cc). These properties can be used with either text or HTML email. Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = "me@my.com" objMessage.To = "test@paulsadowski.com" 'The line below shows how to send using HTML included directly in your script objMessage.HTMLBody = " This is some sample message html. " 'The line below shows how to send a webpage from a remote site 'objMessage.CreateMHTMLBody "http://www.paulsadowski.com/wsh/" 'The line below shows how to send a webpage from a file on your machine 'objMessage.CreateMHTMLBody "file://c/temp/test.htm" objMessage.Bcc = "you@your.com" objMessage.Cc = "you2@your.com" objMessage.Send Sending a text email with an attached file. By repeating the .AddAttachment method you can attach more than one file. When attaching files keep in mind that your recipient may be limited in their ability to receive files above a certain size. Many ISPs limit emails to 8 or 10MB each. You should not send large files to anyone before obtaining their permission. Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = "me@my.com" objMessage.To = "test@paulsadowski.com" objMessage.TextBody = "This is some sample message text." objMessage.AddAttachment "c:\temp\readme.txt" objMessage.Send Sending a text email using a remote server. Sometimes you need to send email using another server. It may be required by your company, or your ISP may be blocking the SMTP port, or your dynamic IP may be blacklisted for being in a dynamic pool. This code shows you how to use a remotes server rather than the SMTP server on your own machine. Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = "me@my.com" objMessage.To = "test@paulsadowski.com" objMessage.TextBody = "This is some sample message text." '==This section provides the configuration information for the remote SMTP server. '==Normally you will only change the server name or IP. objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com" 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objMessage.Configuration.Fields.Update '==End remote SMTP server configuration section== objMessage.Send Sending a text email using authentication against a remote SMTP server. More and more administrators are restricting access to their servers to control spam or limit which users may utilize the server. This example shows you how to use basic authentication, the most commonly used authentication method, when the SMTP server you are using requires it. This code is slightly more complex but not very difficult to understand or work with. Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory. Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network). Const cdoAnonymous = 0 'Do not authenticate Const cdoBasic = 1 'basic (clear-text) authentication Const cdoNTLM = 2 'NTLM Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = """Me"" " objMessage.To = "test@paulsadowski.com" objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication." '==This section provides the configuration information for the remote SMTP server. objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com" 'Type of authentication, NONE, Basic (Base64 encoded), NTLM objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic 'Your UserID on the SMTP server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid" 'Your password on the SMTP server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword" 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 'Use SSL for the connection (False or True) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 objMessage.Configuration.Fields.Update '==End remote SMTP server configuration section== objMessage.Send Send using authentication against a remote server with a file attachment and return receipt and delivery disposition notification requests. In order to use the Delivery Status Notifications (Return Receipt and Delivery Disposition requests) we need to create a reference to the CDO Configuration object in addition to the CDO Message object and set a small number of properties. You must use cdoSendUsingPort (network connection) and not the SMTP server's pickup directory (cdoSendUsingPickup). Const cdoSendUsingPickup = 1 Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification Const cdoAnonymous = 0 Const cdoBasic = 1 ' clear text Const cdoNTLM = 2 'NTLM 'Delivery Status Notifications Const cdoDSNDefault = 0 'None Const cdoDSNNever = 1 'None Const cdoDSNFailure = 2 'Failure Const cdoDSNSuccess = 4 'Success Const cdoDSNDelay = 8 'Delay Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay set objMsg = CreateObject("CDO.Message") set objConf = CreateObject("CDO.Configuration") Set objFlds = objConf.Fields With objFlds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourhost.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your-username" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your-password" .Update End With strBody = "This is a sample message." & vbCRLF strBody = strBody & "It was sent using CDO." & vbCRLF With objMsg Set .Configuration = objConf .To = "test@paulsadowski.com" .From = "me@my.com" .Subject = "This is a CDO test message" .TextBody = strBody 'use .HTMLBody to send HTML email. .Addattachment "c:\temp\Scripty.zip" .Fields("urn:schemas:mailheader:disposition-notification-to") = "me@my.com" .Fields("urn:schemas:mailheader:return-receipt-to") = "me@my.com" .DSNOptions = cdoDSNSuccessFailOrDelay .Fields.update .Send End With In real world usage you'll most likely want to load the text of the email from a file on your computer. The sample code below shows you how to do this. The text can be either plain text or HTML as needed.Our example assumes your text is in the file C:\Temp\MyEmail.txt. This code loads the entire content of that file into a variable, here named BodyText which you can then reference in your CDO code. We assume BodyText is in the scope of your CDO code. 'These constants are defined to make the code more readable Const ForReading = 1, ForWriting = 2, ForAppending = 8 Dim fso, f Set fso = CreateObject("Scripting.FileSystemObject") 'Open the file for reading Set f = fso.OpenTextFile("c:\temp\MyEmail.txt", ForReading) 'The ReadAll method reads the entire file into the variable BodyText BodyText = f.ReadAll 'Close the file f.Close Set f = Nothing Set fso = Nothing Once the text is loaded you can use it in your CDO code something like this... objMessage.TextBody = BodyText or objMessage.HTMLBody = BodyText

Load Recipients from a Database As is the case with most thing in Windows there are many ways to accomplish a task. This is one method of many. Our database is an Access format database that resides on the local disk. The table in our database that we are interested in is called Customers and each record consists of 4 fields named "ID", "Name", "Email", and "Customer", where ID is an autogenerated index, Name is the full name of our customer, Email is the customer's email address and Customer is their customer identification number. We are only interested here in two fields, Name and Email. ID Name Email Customer 1 Bob Jones bjones@test.com 12345 2 Jane Smith jsmith@test.net 12346 Set OBJdbConnection = CreateObject("ADODB.Connection") OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\Acme Inc\Databases\Customers.mdb" SQLQuery = "SELECT Name, Email FROM Customers" Set Result = OBJdbConnection.Execute(SQLQuery) if Not Result.EOF then Do While Not Result.EOF SendMail Result("Name"), Result("Email") Result.MoveNext Loop end if OBJdbConnection.Close As you can see the code is simple. We create a database connection object then open the database and query it for the Name and Email fields of each customer. Those values are passed for each customer to a subroutine that sends the customer an email. Sub SendMail(TheName, TheAddress) Dim objMessage, Rcpt Rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">" Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "This Month's Sales" objMessage.From = """Acme Sales"" " objMessage.To = Rcpt objMessage.HTMLBody = TextBody objMessage.Send End Sub If you are not accustomed to working with databases then this may have seemed a daunting task but as you can see from the code above, it's really quite simple. We've already covered sending email so I'll just mention that this subroutine assumes the HTML body text is a variable called TextBody (see Loading email body text from a file) Also we format the recipient's address in the standard format of "Name" for a more professional look to the recipient.. Remarks As previously stated there are many ways to do this. I've presented one simple method here. Your own use may be with an ODBC connection; it may use mySQL or SQL Server; it may include personalization of the email body text and more. My intent here was to provide you with the basics to get you started. Load data from an Excel Worksheet There may be times when you want to generate an email using data from an application such as Excel. This is one simple illustration of how that could be done. In our example we will be using a Workbook with three columns starting at column A row 1. Each row represents one product in our inventory and the three columns contains the following data about each item: Part Number, Name of Part, Number of Items in Inventory. Graphically our Workbook looks like this: Part Name Stock 4583586 Fliggalhopper 452 5898547 Looplonger 293 This particular script works by walking down each cell of column 1 till it finds an empty cell which it assumes is the end of the list of entries. If your file may contain empty cells then you can use the Worksheet's UsedRange.Rows.Count property to find the last row in which an entry is made. Your code would then use a for loop something like this: rowLast = objSheet.UsedRange.Rows.Count for x = rowStart to rowLast ' do stuff next Function GetData() Dim x, strTemp, objExcel, objWB Set objExcel = Wscript.CreateObject("Excel.Application") Set objWB = objExcel.Workbooks.Open("c:\Acme Inc\Workbooks\Test.xls") Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) ' Make Excel visible while debugging objExcel.Visible = True ' This is the row of our first cell. x = 1 do while objExcel.Cells(x, 1).Value <> "" strTemp = strTemp & objExcel.Cells(x, 1).Value & _ Space(10 - Len(objExcel.Cells(x, 1).Value)) strTemp = strTemp & objExcel.Cells(x, 2).Value & _ Space(50 - Len(objExcel.Cells(x, 2).Value)) strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCRLF x = x + 1 loop ' This will prevent Excel from prompting us to save the workbook. objExcel.ActiveWorkbook.Saved = True ' Close the workbook and exit the application. objWB.Close objExcel.Quit set objWB = Nothing set objExcel = Nothing GetData = strTemp End Function ' This is our main function. Dim strBody Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Inventory report for " & Date objMessage.From = "me@my.com" objMessage.To = "bossman@my.com" strBody = "Part" & Space(6) & "Item" & Space(46) & "Stock" & vbCRLF ' Here we call the function GetData to populate the body text. strBody = strBody & GetData objMessage.TextBody = strBody objMessage.Send The code above will produce an email that looks something like this: To: bossman@my.com From: me@my.com Subject: Inventory report for 3/19/2005 Part Item Stock 4583586 Fliggalhopper 452 5898547 Looplonger 293 This sample sends a simple text email via GMail servers. It's like any other mail but requires that you set the SMTP Port to 465 and tell CDO to use SSL Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory. Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network). Const cdoAnonymous = 0 'Do not authenticate Const cdoBasic = 1 'basic (clear-text) authentication Const cdoNTLM = 2 'NTLM Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "Example CDO Message" objMessage.From = """Me"" " objMessage.To = "me@my.com" objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication and SSL." '==This section provides the configuration information for the remote SMTP server. objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'Type of authentication, NONE, Basic (Base64 encoded), NTLM objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic 'Your UserID on the SMTP server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "You@gmail.com" 'Your password on the SMTP server objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword" 'Server port (typically 25) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'Use SSL for the connection (False or True) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server) objMessage.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 objMessage.Configuration.Fields.Update '==End remote SMTP server configuration section== objMessage.Send

7 Things Never to Say to Your Boss

Everyone has a boss. Even if you "work for yourself," you're still an employee to your client.

A big part of maintaining the boss-employee relationship is to never allow a boss to think you dislike your work, are incapable of doing it, or--worse--consider it beneath you.

[See the best careers for 2010.]

These sound like no-brainers, but many statements heard commonly around the workplace violate these basic rules. Looking for an example? Here are seven heard in workplaces all the time. They may seem ordinary, even harmless. But try reading these from your boss's point of view. You'll see right away why it's smart to never allow these seven sentences to pass your lips:

"That's not my job." You know what? A lot of bosses are simple souls who think your job is to do what's asked of you. So even if you're assigned a task that is, indeed, not your job, refrain from saying so. Instead, try to find out why your boss is assigning you this task--there may be a valid reason. If you believe that doing the task is a bad idea (as in, bad for the company) you can try explaining why and suggesting how it could be better done by someone else. This may work, depending on the boss. In any case, remember that doing what's asked of you, even tasks outside your job description, is good karma.

[See the 50 worst job interview mistakes.]

"It's not my problem." When people say something is not their problem it makes them look like they don't care. This does not endear them to anybody, especially the boss. If a problem is brewing and you have nothing constructive to say, it's better to say nothing at all. Even better is to pitch in and try to help. Because, ultimately, a problem in the workplace is everyone's problem. We're all in it together.

"It's not my fault." Yet another four words to be avoided. Human nature is weird. Claiming that something is not our fault often has the result of making people suspect it is. Besides, what's the real issue here? It's that something went wrong and needs to be fixed. That's what people should be thinking about--not who is to blame.

"I can only do one thing at a time." News flash: Complaining you are overworked will not make your boss feel sorry for you or go easier on you. Instead, a boss will think: (1) you resent your job, and/or (2) you aren't up to your job. Everybody, especially nowadays, feels pressured and overworked. If you're trying to be funny, please note that some sarcasm is funny and lightens the mood. Some just ticks people off.

[See how to answer 10 tricky interview questions.]

"I am way overqualified for this job." Hey, maybe you are. But the fact is, this is the job you have. You agreed to take it on and, while you may now regret that decision, it's still your job. Complaining that it's beneath you only makes you look bad. Plus, coworkers doing similar jobs may resent and dislike you. And guess what? Bosses will not think, "Oh, this is a superior person whom I need to promote." Nope, they'll think, "What a jerk."

"This job is easy! Anyone could do it!" Maybe what you're trying to convey here is that you're so brilliant your work is easy. Unfortunately, it comes off sounding more like, "This work is stupid." Bosses don't like hearing that any work is stupid. Nor do they really like hearing that a job is easy peasy. It belittles the whole enterprise. If a task is simple, be glad and do it as quickly as you can. Even "stupid" work needs to get done.

"It can't be done." Saying something can't be done is like waving a red flag in a boss's eyes. Even if the thing being suggested truly is impossible, saying it is can make you look ineffectual or incapable. Better to play detective. Why is the boss asking you to do whatever it is? What's the problem that needs to be solved? What's the goal? Search for doable ways of solving that problem or reaching that goal. That's what bosses really want. Most of them do not expect the impossible.

Last words: When in doubt, remember that silence really is golden.