excel -senior vba lesson ,from 0 to 100

							                                   VISUAL BASIC FOR APPLICATIONS
                                LESSON THIRTYEIGHT: A WORD e-mail program!

EXAMPLE PROGRAM: word_email.doc

This lesson looks at the examples included in the above document.

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
or..
 “Send Using No Dialog”

SEND USING 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.

SEND USING NO DIALOG

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 stripit(MyTable.Cell(rowCount, 4), 2) <> ""
r.Address = stripit(MyTable.Cell(rowCount, 4), 2)
r.RecipClass = MAPI_TO
msg.Subject = stripit(MyTable.Cell(rowCount, 2), 2) + " " + stripit(MyTable.Cell(rowCount, 1), 2)
msg.NoteText = stripit(MyTable.Cell(rowCount, 3), 2)
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
Wend
'Change the path back from the email application
ChDir MyPath
MsgBox "FINISHED SENDING MULTIPLE EMAILS!"
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 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.

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


                       [To Lesson Thirtyseven] [To Part Five Index Page] [To Lesson Thirtynine]