• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

How to send e-mail from VB Access ?

Hi there,

I was looking for a way to send e-mails from an application in Access.

There's one detail on that, our Exchange Server does not support SMTP service.

Our Exchange Server does support RPC over HTTP, I was wondering to know if is there any option I can explore with this resource.

Thanks a lot
Allan Martins
Allan Martins
  • 2
2 Solutions
Jim P.Commented:
Have you looked at the

DoCmd.SendObject command in VB/VBA?

It depends on how automated you want to get.
Read the following carefully and do the needful :

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

The SendMessage function
The Windows API documentation shows the following syntax:

LRESULT SendMessage(
    HWND hWnd,          // Handle of destination window
    UINT Msg,           // Message to send
    WPARAM wParam,      // First message parameter
    LPARAM lParam);     // Second message parameter
The hWnd parameter is the window to which the message is sent. The Msg parameter is the message numberwhich is usually a constant such as LB_FINDSTRING or WM_COPY. The WPARAM and LPARAM types are actually aliases for 32-bit integers (Long in Visual Basic). The wParam and lParam parameters differ for each message, as does the return value; you must look up the specific message to see what they mean. Often, wParam or the return value is ignored.

The lParam parameter is particularly interesting. It is a 32-bit integer, which happens to be the size of a pointer, and this value is often used to pass a pointer to a string or a UDT. In other words, lParam is typeless. In Visual Basic, that means that the SendMessage declaration contains the As Any type. Alternatively, you can define several type-safe declarations with different aliases.

You need only one SendMessage Declare statement to cover all your bases:

Declare Function SendMessage Lib "USER32" Alias "SendMessageA" ( _
    ByVal hWnd As Long, ByVal Msg As Long, _
    wParam As Any, lParam As Any) As Long
Using this Declare you can pass any value by reference. To pass a String or a Long by value, you must give the ByVal keyword in the call. Sometimes you might even have to pack two Integers into a Long and pass the Long by Value. Or you might receive, by reference or in the return value, two Integers packed in a Long. Notice that both wParam and lParam are passed by reference because either might take a reference argument.

Of course, the Declare only works for ANSI strings, and I try to make all my code portable to Unicode in anticipation of the day when ANSI strings fade to just a bad memory. The Windows API type library has equivalent entries, but for reasons described in "Unicode Versus Basic" in Chapter 2, you cant use the type library equivalent of As Any (void *) to represent strings. Therefore, the type library provides a separate SendMessageStr entry that takes a string lParam. Its equivalent to the following Declare statement:

Declare Function SendMessageStr Lib "USER32" Alias "SendMessageA"
    (ByVal hWnd As Long, ByVal Msg As Long, _
    ByVal wParam As Long, ByVal lParam As String) As Long
Notice that SendMessageStr takes a ByVal Long as its wParam parameter despite the theoretical possibility that some message might take a ByRef wParam and a string lParam. I checked and there is no such message. If you find one, sue me.

I also provide a SendMessageVal that you can use in place of SendMessage with ByVal in the call.

Declare Function SendMessageVal Lib "USER32" Alias "SendMessageA"
    (ByVal hWnd As Long, ByVal Msg As Long, _
    ByVal wParam As Long, ByVal lParam As Any) As Long
Lets look at an example. Assume you have a multiline text control named txtEditor. You can request the selection offset and length with these lines:

iPos = txtEditor.SelStart
iLen = txtEditor.SelLength
In order to get this information, Visual Basic sends the EM_GETSEL message to the txtEditor window procedure with a call such as the following:

Dim iStart As Long, iEnd As Long
Call SendMessage(txtEditor.hWnd, EM_GETSEL, iStart, iEnd)
If the window procedure for TextBox controls were written in Visual Basic, it would handle this message in a Select Case block:

    ' Put offsets of start and end into variables
    wParam = iStartSel
    lParam = iEndSel
Visual Basic returns the start of the selection as the SelStart property. It subtracts the start of the selection from the end of the selection and returns the result as the SelLength property. The Visual Basic internal code looks something like this:

SelStart = iStart
SelLength = iEnd - SelStart
If you have time to burn, you can do this yourself by calling SendMessage instead of using the SelStart and SelLength properties.

What if you want to undo the last editing change? Simplejust call the Undo method. Unfortunately, the TextBox control doesnt have an Undo method. But youve seen it in Notepad; Windows must have an Undo message. Sure enough, if you check the Windows API documentation, youll find an EM_UNDO message. You can undo the last editing change with this simple statement:

Call SendMessage(txtEditor.hWnd, EM_UNDO, ByVal 0&, ByVal 0&)
You could also do this with the SendMessageVal alias:

Call SendMessageVal(txtEditor.hWnd, EM_UNDO, 0&, 0&)
Just to show all the options, you could also use this statement:

Call SendMessage(txtEditor.hWnd, EM_UNDO, 0&, 0&)
This works, but perhaps not the way you expect. SendMessage takes ByRef parameters, which means it passes the addresses of variables. But a literal constant doesnt have an address. Visual Basic fakes it by creating temporary variables, stuffing constant zeros into them, and passing the addresses of the temporaries. Dont make your programs do this extra work.

This is just the beginning of what you can do with messages. You can check whether theres anything to undo. You can get the number of lines of text, the current line, or the current column. You can set the indent spacing for tab characters. You can do many other editing tasks that Visual Basic doesnt support directly. Well try many of these editing operations with Edwina the editor, introduced in Chapter 9.

The same principle holds for other controls such as list boxes and combo boxes: Visual Basic supports the most common operations through methods, events, and properties; you must handle others with SendMessage.

Bm keshav
Allan MartinsAuthor Commented:
jimpen, I am looking a way to make the mail sending proccess automated (generating, and sending) - but it all should start once a user open the application and hit "Send Reports" button.

BMKESHAV, Thanks for this info, I will dig on it between today and tomorrow and I will let you know what I was able to do with it.

Allan MartinsAuthor Commented:
Sorry for taking too long to reply, I was out on a project and had limited access to internet. At the end, I user another solution using a local SMTP server to send the message, the SendObject was helpful, but I had to implement a SendMail function that uses the Outlook profile to send these messages.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now