HELP - Send Email From Access Query To Oulook

[zalouma]

Hi, I am new here and to access, i am using 2007, I would first like to thanks everyone on this forum for being alot of help to myself before i even register to post this question, I have learned alot from here, and Its great to see many people are helping newbie like me improve

My issue that i am having is i have a query in my database with 6 Fields:
1- [Order ID]
2- [Country]
3- [Client Number]
4- [Order Ref]
5- [Order Id]
6- [E mail]

In the query i have under Order ID [Please Chooser Order ID] so that people would filter what order they want

What i want is once people enter Order ID , it will open outlook with a composed e mail as well as placing the email address from [e mail field] with the following

Subject is : [Clientname] subject in [Country] from the query

then in the body i want it to import some data:
Your Order Number: [order Id]/[order refer] from the query
then i would write the body text here

Any one can help me to do that

i dont know what to do or where to start

Thank you so much in advance

Regards,
Sami

 

[Sketchin]

I hope someone helps you with this, as I have the same problem!

 

[BigHappyDaddy]

I have an email function that will create the email, you just have to provide the email, subject line, etc as arguments. I can't post the code now, but will try to do it in the morning.

 

[Sketchin]

That would be most helpful, thanks!

 

[BigHappyDaddy]

Sorry, my work computer is being difficult this morning. I am unable to read any of the posts in this thread. But, here is the function I promised:

Public Function SendEmail(Optional ByVal strTo As String, Optional ByVal blnSendNow As Boolean, _
                          Optional ByVal strCC As String = "", Optional ByVal strBCC As String = "", _
                          Optional ByVal strSubject As String = "", Optional ByVal strBody As String = "", _
                          Optional ByVal strAttachement As String = "") _
                As Boolean
'* Function Name:SendEmail
'* Parameters: strTo, blnSendNow, strCC, strBCC, strSubject, strBody, strAttachement 
'* Author: BigHappyDaddy
'* Date: September 11, 2012
'* Purpose:
'* Sends an email using the attributes passed into the function. Returns TRUE when email is successfully sent.
 
    Dim objOut As Object
    Dim objMail As Object
 
    Set objOut = CreateObject("Outlook.Application")
    Set objMail = objOut.CreateItem(olMailItem)
 
    On Error Resume Next
    With objMail
        .To = strTo
        .CC = strCC
        .BCC = strBCC
        .Subject = strSubject
        .Body = strBody
        If strAttachement <> "" Then
            .Attachments.Add strAttachement
        End If
        If blnSendNow Then
            .Send
        Else
            .display
        End If
    End With
 
    Set objOut = Nothing
    Set objMail = Nothing
 
    SendEmail = True
End Function

This has vast opportunites for improvements, but I hope it can get you started.

 

[Sketchin]

I think the part I will still struggle with is getting the emails from my query, into the code.

 

[BigHappyDaddy]

I would make my query into a recordset, then you could step through the returned record(s) and then call the function above using the data from the query.

I would insert a small example, but I am running short on time. I sure if you searched this site, you could find a number of examples.

I hope this points you in the right direction.

 

[Sketchin]

Ya, I read somewhere about concantenating forms or something...will do some searching and come across the answer.

Thanks again for the help brother!

 

[BigHappyDaddy]

A simple example of the recordset example:

Public Sub EmailQuery()
   Dim strQuery as string
   Dim blnEmailSent as Boolean
   Dim db as DAO.database
   Dim rs as DAO.Recordset
 
   strQuery = "Query Name or SQL Statement goes here"
   set db = currentdb()
   set rs = db.openrecordset(strQuery,dbOpenDynaset)
 
   do until rs.eof
      blnEmailSent = SendEmail ( rs("EmailField"), false)
      rs.movenext
   Loop
 
   'Addtional code as needed goes here
 
End Sub

Be sure to change the value for strQuery to the name of your query. And change 'EmailField' (double quotes are still required) to the name of the field in the query that contains the email address.

Again, I hope this gets you closer...