Sending an email in MS Access 2010 using VBA code

[Lou_Reed]

I am trying to write a VBA program that will allow someone using the MS Access 2010 database to send an email. That seems rather straight forward from a quick goggle search.

However, that is not all I want it to do. The idea is when a manager is looking into a organization's database and seeing the people that are coming to a specific meeting, and upon noticing that someone was not invited to the meeting. He can then to be able to send that person an invite from that screen (in MS Access) that uses the MS Meeting manager which is part of MS Outlook. That makes things a little more complicated.

As I said I can find many example online creating a VBA program to send an email to a person for example a congratulation for meeting some goal in the organization. In that example the name and the email address are in the VBA code! That is too specific.

This is not what I want. I will have a table of employees in the organization, with their associated email addresses. That would be the table that I want the VBA to reference for all its information.



Respectfully,



Lou Reed

[ranman256]

make a table of emails, tEmails. you could even have groups,
eAddr, person, group
jo@work.com, jo smith, Acct
bob@work.com, bob jones, Acct
jo@work.com, jo smith, BigMeet

you can add ,subtract users at will.
make a form, put a listbox on it, connect it to the tEmail
add a combo box with the differnt groups. (a query to pull unique Group from tEmails table)
picking a group will filter the names.

make a box to put in the email message,
a button to scan thru the list box and send the message to each.

'------------
Public Sub btnSend_click()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer

For i = 0 To lstEAddrs.ListCount - 1
   vRpt = lstEAddrs.ItemData(i)
   lstEAddrs = vRpt
   vTo = lstEAddrs.Column(2)
   
   vBody = me.txtMessage
   vSubj = me.txtSubj
   
   Call Email1(vTo, vSubj, vBody)
Next
End Sub


'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark OUTLOOK OBJECT LIBRARY in the vbE menu, Tools, References
'-------

Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody,optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody

    If Not Ismissing(pvFile) Then  .Attachments.Add pvFile, olByValue, 1
    
.Send
End With

EmailO = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function

[orange]

See if this is helpful
Blueclaw has a lot of sample code MsAccess/vba

[Lou_Reed]

I assume that the statement in the code is an error:

dim I as integer

It should be

Dim I as integer
.

Also when creating the button to activate the send email sequence of events what is the conventional way to do this? I can think of two:

I can have a button with the caption "send email", next to each name on the personnel roster with a person's name and email address.

I can also have a single button located somewhere on the form that can be clicked to "send email". In that case the program must somehow find the
active record with name, email address and anything else so that the program selects the right person and their associated email address.

The first requires a send email button on the record line for each person and the second requires only one button which is somehow/someway connected to the active email.

I am not sure which is the standard way to do this. I have seen it done both ways.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed