excel -senior vba lesson ,from 0 to 100 by Guihong


More Info
									                                   VISUAL BASIC FOR APPLICATIONS
                                LESSON THIRTYNINE: An EXCEL e-mail program!

EXAMPLE PROGRAM: excel_email.xls

This lesson looks at the examples included in the above EXCEL workbook.

The example has two sections.
1. Sending a single email using a dialog, or sending it without a dialog
2. Sending multiple emails without using a dialog.

In the first section, a form has been created with two choices available.
 “Send Using Dialog
 “Send Using No Dialog”


lReturn = MAPISendMail(0, 0, udtMessage, udtRecip, udtFile, MAPI_DIALOG, 0)

The sixth value passed to “MAPISendMail” determines whether or not a dialog is to be used. A value of “0”, as
below, indicated that no dialog is to be used.


lReturn = MAPISendMail(0, 0, udtMessage, udtRecip, udtFile, 0, 0)

In the second section, the e-mail is send automatically using the following procedure.

Public Sub Auto_Mail_Send()
Dim lresult As Long
Dim msg As MapiMessage
Dim lSess As Long
Dim r As MapiRecip
Dim sName As String
Dim f As MapiFile
Dim rowCount As Integer
Dim Count As Integer
Dim MyPath As String
'Get the currect path
MyPath = CurDir
rowCount = 2
Set MyTable = ActiveDocument.Tables(2)
'the last two characters need to be removed from the
'table cell values used below
While ActiveSheet.Cells(rowCount, 4).Value <> ""
r.Address = ActiveSheet.Cells(rowCount, 4).Value
r.RecipClass = MAPI_TO
msg.Subject = ActiveSheet.Cells(rowCount, 2).Value + " " + ActiveSheet.Cells(rowCount, 1).Value
msg.NoteText = ActiveSheet.Cells(rowCount, 3).Value
msg.RecipCount = 1
lresult = MAPILogon(0, "", "", 0, 0, 0)
lresult = MAPISendMail(0, 0, msg, r, f, 0, 0)
lresult = MAPILogoff(0, 0, 0, 0)
rowCount = rowCount + 1
'Change the path back from the email application
ChDir MyPath
Set MyTable = nothing
End Sub

   The first thing you might notice is that the names for user defined types have been changed. For example,
    “Dim f As MapiFile”. The main reason is to make the code easier to read, especially if you are passing lots of
    variables to a function.
   The next thing you might notice is that the current directory is read into a variable at the beginning of the
    procedure, then the current directory is set to this variable at the end of the procedure. This is because the
    current directory is changed to the your email client directory. So when you go to save later, you will save to
    the wrong folder. Its not funny trying to work out why the latest changes you made in a document have
    disappeared all of a sudden.
   You will notice that the “MAPISendMail” function is preceded by a “MAPILogon” function, and followed by
    a “MAPILogoff” function. This is necessary if you are going to send lots of e-mail to prevent errors.
   The rest of the code is pretty straight forward.

You can easily see the possibilities for this automatic sending of e-mails.

Create a more dynamic message to send, including data such as a business letterhead etc.

                         [To Lesson Thirtyeight] [To Part Five Index Page] [To Lesson Forty]

To top