Email from Access

[Tatiana1981]

Hello All!!

I am new in Access and urgently need a help.
I have a DB, while one of the tables contains email address and name in each record.
I need to be able to send same email to everyone who is in the table with only one change - the name.
For example:
1st record: name: John Smith email: john@email.com
He will receve:
Dear John,
Thank you for help
Best Regards
Me
2nd record: name Sandra Lewis email: sandra@googl.com
She will receive:
Dear Sandra,
Thank you for help
Best Regards
Me

How and which function should i use/build in order to be able to perform the task.
Thank you very much for every oissible help.
Best Regards,
Tatiana

 

[reclusivemonkey]

Why do you want to use the name "John" when sending an email to "Sandra"?

 

[Tatiana1981]

Sorry, my mistake. Already fixed. Thank you.

 

[Jibbadiah]

Something like the following.

Public Function SendEmail()

PROC_DECLARATIONS:
    Dim olApp As Outlook.Application
    Dim olnamespace As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    Dim dbs As Database
    Dim rstEmailDets As Recordset
    Dim strSender As String
    Dim strRecipient As String
    Dim strEmail As String

PROC_START:
   On Error GoTo PROC_ERROR
   
PROC_MAIN:
    DoCmd.SetWarnings False

Set dbs = CurrentDb
    
    'Put on hourglass
    DoCmd.Hourglass True

    'Collect the email details in a recordset to use in the email loop below
Set rstEmailDets = dbs.OpenRecordset("SELECT PersonsName, PersonsEmail FROM TableName;")
                                            
    With rstEmailDets
        .MoveFirst
        Do While Not rstEmailDets.EOF
    
        'Set parameters for email: recipient name, recipient email
        strRecipient = Nz(rstEmailDets("PersonsName"), "")
        strEmail = Nz(rstEmailDets("PersonsEmail"), "")

    
        With dbs
            
            'Create a new instance of an Outlook Application object
            Set olApp = New Outlook.Application
            Set olnamespace = olApp.GetNamespace("MAPI")
            Set olMail = olApp.CreateItem(olMailItem)
                
            With olMail
                
                'Email Details
                .To = strEmail
                .Subject = "Greetings " & strRecipient
                .Body = vbCrLf & _
                    "Dear " & strRecipient & "," & vbCrLf & vbCrLf & _
                    "Put your message Here" & vbCrLf & vbCrLf & " "
                .Importance = olImportanceNormal
                .Send
                
        End With
        .MoveNext
        Loop

    End With
    
MsgBox "All Emails have now been sent, Thank you for your patience"

PROC_EXIT:
    ' Perform cleanup code here, set recordsets to nothing, etc.
    On Error Resume Next
    DoCmd.RunCommand acCmdWindowHide '(hides the database window)
    Set olApp = Nothing
    Set olnamespace = Nothing
    Set olMail = Nothing
    Set dbs = Nothing
    Set rstEmailDets = Nothing
    DoCmd.Hourglass False
    Exit Function

PROC_ERROR:
    If Err = -2147467259 Then
        MsgBox "You have exceeded the storage limit on your mail box. Please delete some items before clicking OK", vbOKOnly
        Resume
    End If
    
    If Err = 2501 Then
        MsgBox "You have attempted to cancel the output of the emails." & vbCrLf & _
            "This will cause major problems." & vbCrLf & _
            "Please be Patient"
        Resume
    End If
    
    End Function

 

[Tatiana1981]

Thank you very much.
Another question..may be stupid one...
Should i do the following:
build a form, where will be one button only , something like "send email" and field for subject and body for mail.
The onPress event i should give the said function and use E_subject.text and E_body.text in needed places?
How does the program know which table from all i have in Db to use?
Thank you VERY much

 

[Jibbadiah]

Yep, you are pretty much on track.

You are creating a recordset where you will extract name and email and loop through them one at a time. The table that you use is specified in the select statement below. Change PersonsName, PersonsEmail and Tablename to those used in your database.

("SELECT PersonsName, PersonsEmail FROM TableName;")

You could do what you suggest with the form.

You could put in:

dim strMessage as string

strMessage = forms![Formname].[TxtControlBoxName]

and then include...

.Body = strMessage

HTH

J.

 

[Tatiana1981]

Thank you. I will try and let you know.

 

[Tatiana1981]

Another question...from where and how do i call the written Function?
there is no such outlook.namespace and outlook.application.......where do i have to write it?

 

[Jibbadiah]

Sorry Tatiana,

I should have mentioned.

Paste the code into a vb window, either as a module that you call from a command button, or the class module of the form.

Whilst viewing the vb code goto the tools menu, references, and tick the reference for microsoft Outlook.

J.

 

[Tatiana1981]

Thanks again.
And how will I call for the module? In the procedure of PressButton just to write its name??

 

[Jibbadiah]

Yep.

If you add it to the on click event of a command button, and the function in your module is called SendEmail. You would put the following:

Private Sub EmailMessage_Click()

SendEmail

End Sub

The best way to check this code while it runs is to step through it. You do this by opening the code window and pressing F8. This allows you to step through your code one line at a time. If you hover the cursor over any parameters (like strEmail) it should show you the current value for that parameter. This is the best way to discover errors and understand what your code is doing as it runs.

Give it a try and tell me how you get on.

J.

 

[Tatiana1981]

I appreciate your help sooooooo much!! Thank you!!
This is what i did (in the procedure) but when i click the button, it gives me an error " Compile error, expected variable or procedure, not module"

 

[Tatiana1981]

And another problem i have is: when i make F8 on the module code, it gives me an error message on the line with .MoveNext. Error is "Methode or data member not found"

 

[Jibbadiah]

Sorry Tatiana,

I doctored some old code.
If you delete that line that says "With dbs".
This isn't necessary.

J.

 

[Jibbadiah]

So just to be sure.
Go to modules and paste the big code above in a new module.
Delete the line that says "with dbs".
Change all instances of PersonsName, PersonsEmail and TableName with the values that you are using in your db.
Then go to Debug menu, Compile and save all modules.
This should tell you if you have any errors.
I have done this and the code seems fine.

Tell me how you get on.

 

[Tatiana1981]

Ok...so here what i did:
I wrote all the code in the OnClick procidure.
the line: strMessage = forms![Formname].[TxtControlBoxName]
changed to: strMessage = txt.text
same with the subject.
Deleted and changed all you wrote above and.............it works!!!!
Thank you soooo much.
The only problem is that for each email address he asks my permission to use the outlook program. Is there anyway to avoid it?
Thank you again.

 

[Jibbadiah]

Tatiana,

Pleased to hear that it is working.
I'm afraid that my usefulness has come to an end.
The company that I work for is still using Access 97 and Windows NT.
I think that the problem that you are experiencing is a new security feature of the windows software.
I am pretty sure that others have found a workaround for it, and you will probably find it if you search this forum using the warning message that you get as your search criteria.

Good luck.

J.

 

[Jibbadiah]

Check out this thread...

http://www.access-programmers.co.uk/forums/showthread.php?t=97854&highlight=outlook+security

 

[checoturco]

Hi all,

i use this function and it works ok.
i try add this code ( red code )to send a attachment with the mail

'Email Details
.To = strEmail
.Subject = "greetings " & strRecipient
.Attachments.Add Source:="C:\test.pdf"
.Body = vbCrLf & _
"Mr. " & strRecipient & "," & vbCrLf & vbCrLf & _
"mensagem de teste" & vbCrLf & vbCrLf & " "
.Importance = olImportanceNormal
.Send

now, the application blocks. i have the add a object for attachement.add?

tks all

 

[Tatiana1981]

Thank you James!!!
I really appreceate your help! It helped me a lot.
thank you.