Email from Access

[Jibbadiah]

No Problem Tatiana - I hope it works out for you.

Checoturco - change your syntax to the following:

.Attachments.Add ("C:\test.pdf")

J.

 

[checoturco]

it works, tks

 

[Creegfire]

Hello All!

Well ive read this topic and tried it out but unfortunaly it doesnt work for me. I have a problem with the loop. How can i fix that problem?

 

[Jibbadiah]

Creegfire,

Your problem is a little vague.

Can you post your exact code and pinpoint the line of code that has the problem and I will take a look.

You can establish which line is having the problem by pressing F8 to move through the code.

J.

 

[Creegfire]

Option Compare Database

Public Function SendEmail()

PROC_DECLARATIONS:
    Dim olApp As Outlook.Application
    Dim olnamespace As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    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 Voornaam, E-mail FROM Tabel_Rubicon_Gebruikers;")
                                            
    With rstEmailDets
        .MoveFirst
        Do While Not rstEmailDets.EOF
    
        'Set parameters for email: recipient name, recipient email
        strRecipient = Nz(rstEmailDets("Voornaam"), "")
        strEmail = Nz(rstEmailDets("Tabel_Rubicon_Gebruikers"), "")
                
            With olMail
                
                'Email Details
                .To = strEmail
                .Subject = "Bevestiging Incident voor " & strRecipient
                .Body = vbCrLf & _
                    "Hallo " & strRecipient & "," & vbCrLf & vbCrLf & _
                    "Wij zullen je zo spoedig mogelijk helpen" & 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

Well some things in it are from Holland.

But with this sort of "script" i have a few questions.

1. I want that my outlook is opened and that the e-mail adres and subject at least has been filled up.
2. I want to create something with the body that the body is automaticly filled in with the details of a form/datanbase.
3. What do i need to change that i first see the outlook windows and that then the message automaticly will be sended to the right person?

***EDIT***

Maybe ive still not given enough information

Im using Office XP btw...

Anyway, i have a form with details about incidents here in the company. What i want is when i press a button that automaticly e-mail message had been sended to the user that has called to our helpdesk. So that when outlook starts that the mail to field has been filled with the e-mail of that user and that the subject has the next title: Reply to the user <== so i mean that the username automaticly has been filled up and then that in the body (the mail field) has the information about the incident (Who has taken the incident, how did it come, a sort of date, the problem etc. etc. etc.) Maybe i have explained now a bit what i want to get? THANKS IN ADVANCE ANYWAY!

 

[Jibbadiah]

Creegfire,

A few things.

- Firstly, look at your code and ensure that tools, references, microsoft outlook is ticked.
- include End Function at the end of your code.
- include dim dbs as database in the PROC_DECLARATIONS section
- it doesn't like the word E-mail, so I changed this to Email
- in this line

strEmail = Nz(rstEmailDets("Tabel_Rubicon_Gebruikers"), "")

... you should actually have been referencing the email, rather than the table... so it should be strEmail = Nz(rstEmailDets("Email"), "")
- you failed to create your outlook objects before using them

With olMail

New code below... try it and see how you get on.

Public Function SendEmail()

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

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 Voornaam, Email FROM Tabel_Rubicon_Gebruikers;")
                                            
    With rstEmailDets
        .MoveFirst
        Do While Not rstEmailDets.EOF
    
        'Set parameters for email: recipient name, recipient email
        strRecipient = Nz(rstEmailDets("Voornaam"), "")
        strEmail = Nz(rstEmailDets("Email"), "")
                
            Set olApp = New Outlook.Application
            Set olnamespace = olApp.GetNamespace("MAPI")
            Set olMail = olApp.CreateItem(olMailItem)
                
            With olMail
                
                'Email Details
                .To = strEmail
                .Subject = "Bevestiging Incident voor " & strRecipient
                .Body = vbCrLf & _
                    "Hallo " & strRecipient & "," & vbCrLf & vbCrLf & _
                    "Wij zullen je zo spoedig mogelijk helpen" & vbCrLf & vbCrLf & " "
                .Importance = olImportanceNormal
                .Save
                .Display
                
                If MsgBox("Email Saved, if you like the send it click yes, otherwise click no", vbYesNo, "Send Email?") = vbYes Then
                    .Send
                Else: GoTo PROC_EXIT
                End If
                
        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
    
    MsgBox "error no: " & Err.Number & " Error Description: " & Err.Description
    Resume PROC_EXIT
    
End Function

Open this code and press F8 through the code to see what it has problems with and what the values are at each stage.

 

[Creegfire]

Well thanks for the reply and ive tested it out ofcourse.

When ive put this "code" into a module of microsoft access and then i go to my form to perform the action it notice that it still doenst work somehow.

I have read that you have edded the line:

Dim dbs As Database

Thats already the first thing what happens wrong. It gives the warning compilefault! By a user defined infotype isnt defined or something like that

So i have deleted that line and i moved one what doenst work in the code.

And when i removed that line and i perform the action again with the button it gives me now the warning:

Error no: 13 Error Discription: Types do not match

Something isnt good and i just cant find out whats wrong

And what also funny is when i press the button and i use my code what ive posted before a mousepointer has changed and it says that it is something loading FOR A LONG TIME!

 

[Jibbadiah]

Creegfire,

This code works for me. In fact I recreated your table and field names and while testing I accidentally sent 3 emails to fictitious email accounts... phil@msn.com isn't fictitious !! ... and he didn't know German!!

I have the following references libraries ticked:
visual basic for applications
microsoft access 8 object library
microsoft 3.51 dao object library
microsoft outlook 8 object library

If it still doesn't work after selecting them, then zip a copy of your db and post to this site. Ensure it is Access 97, and don't include any confidential data.

J.

 

[Creegfire]

Creegfire,

This code works for me. In fact I recreated your table and field names and while testing I accidentally sent 3 emails to fictitious email accounts... phil@msn.com isn't fictitious !! ... and he didn't know German!!

I have the following references libraries ticked:
visual basic for applications
microsoft access 8 object library
microsoft 3.51 dao object library
microsoft outlook 8 object library

If it still doesn't work after selecting them, then zip a copy of your db and post to this site. Ensure it is Access 97, and don't include any confidential data.

J.

Ive read your post and it still doesnt work its very wierd...

Well, then ill try to post my db here on the forum.
If it is possible... can i also send it directly to you?

C.

EDIT

Dont forget that i have Office XP

 

[Jibbadiah]

Creegfire,

I am happy for you to PM me with your db.
Or alternatively send it to <removed my email... sorry>

J.

 

[colette]

sending emails

Hi,
docmd.sendobject is one way to send out emails. With Outlook 2003's email security though sending emails from Access has become more tedious. It only allows one email at a time and so lists of people cause you to hit a button over and over again. There are some third party tools out there and I believe I once found a nice email process on this site.
Colette

 

[thecheww]

Function call error

Hi,
I read this forum and this code is what i wanted to implement in my project. I followed all your steps. I created a module name SendEmail with your code. Changed all my field names and tablenames. Check for error. I then created a command button with a on click event.

Private Sub Command30_Click()
SendEmail
End Sub

It is giving me "Compile error: Expected variable or procedure, not module"

Thank you veyr much for your help.

Ed

 

[Jibbadiah]

Ed,

As an alternative you could try this...

Public Function SendEmail(strEmail As String, strSubject As String, strEmailBody As String, blnSend As Boolean, Optional strAttachment As String)

PROC_DECLARATIONS:
    Const sProc_Name  As String = "EmailTASSurvey"
    Dim olApp As Outlook.Application
    Dim olnamespace As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    Dim strThisMonth As String
    
PROC_START:
   On Error GoTo PROC_ERROR
   
PROC_MAIN:

    DoCmd.SetWarnings False
            
    '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 = strSubject
        'Email Content
        .Body = "" & strEmailBody & vbCrLf
        .Importance = olImportanceNormal
        .ReadReceiptRequested = True
            'If attachment is sent then attach
            If Len(strAttachment) > 0 Then
                .Attachments.Add (strAttachment)
            End If
            'If bln Send = true then Send, otherwise just save
            If blnSend = True Then
                .Send
            Else
                .Save
                .Display
            End If
        End With

    
    'Update status on status bar
    vStatusBar = SysCmd(acSysCmdClearStatus)
    
Proc_Exit:
    ' Perform cleanup code here, set recordsets to nothing, etc.
    On Error Resume Next
    Set olApp = Nothing
    Set olnamespace = Nothing
    Set olMail = 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
    Else 
         MsgBox Err.Number & " - " & Err.Description
    End If

    Resume Proc_Exit
   
End Function

And you would call the function with something like the following...

Private Function Ed
 Dim strFilename As String
    strFilename = "c\temp\Attachment.xls"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryOutputForAttachment", strFilename, True

    SendEmail "phil@msn.co.uk", "Folks, " & vbCrLf & vbCrLf & "This is my sample email." & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Ed" & vbCrLf, False, strFilename

End Function

This will allow you to start simple and work up to more complicated. It may help you find the error in the other code. Don't forget to add Outlook to your references under the tools menu.

HTH

James