How to send e-mail from VB Access ?

Posted on 2009-04-17
Last Modified: 2013-11-30
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
Question by:AllanMartins
    LVL 38

    Accepted Solution

    Have you looked at the

    DoCmd.SendObject command in VB/VBA?

    It depends on how automated you want to get.
    LVL 6

    Assisted Solution

    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:

    Case EM_GETSEL
        ' 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
    LVL 2

    Author Comment

    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.

    LVL 2

    Author Closing Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now