Sending emails from Accsess 2

touqir · Sep 21, 2005

Hi All!

I want to send email using the email address from a access table. I want that all fields againts that email address should be in email body. I tried to do that but the error coming
"Can't find the field '|' reffered to in your experession"

I using the following routine

***********************************************************
Private Sub Command202_Click()
On Error GoTo Err_Command202_Click

'Declaration of Variables
Dim email As String
Dim cc As String
Dim origin As String
Dim body As String
Dim dbs As DAO.Database
Dim rs1 As DAO.Recordset

'Open and populate the recordset with required data
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("Select * FROM [PO Master]WHERE [PO No]= " & [PO No].Value)

'Gather the information about the e-mail
email = rs1("Email")

'Set up the body of the e-mail to add all fields
body = [PO No]
'Send the e-mail to the recipients
DoCmd.SendObject acSendForm, , acFormatTXT, email, cc, , "Your PO ", body, True

'Add a new record
DoCmd.GoToRecord , , acNewRec

Exit_Command202_Click:
Exit Sub

Err_Command202_Click:
MsgBox Err.Description
Resume Exit_Command202_Click

End Sub
***********************************************************
If anybody can help me out. I'll be thankful.

Remou · Sep 22, 2005

Hi
I tried the code above, and it works for me. You could try checking your field names and also that the fields referred to contain the right information.

(It seems from the above that you are entering data, emailing the record, entering the next person and so on. However, I do not see a point at which you save the record. If I use the code above on an unsaved record, I get an error, though not the one that you get.)

touqir · Sep 22, 2005

Dear Remou! thnx. for ur reply. I am also using the same field name as they are in my database but still having the same error. I changed it as follows;

Private Sub Command202_Click()
On Error GoTo Err_Command202_Click

'Declaration of Variables
Dim email As String
Dim cc As String
Dim origin As String
Dim body As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strEmailAddresses As String

'Open and populate the recordset with required data
Set dbs = CurrentDb
strSQL = "SELECT * FROM pomaster"

' Open up the recordset
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'Loop through all records in table and concatenate the e-mail addresses
Do Until rec.EOF

strEmailAddresses = strEmailAddresses & rec![Emailadd]
email = strEmailAddresses
DoCmd.SendObject acSendNoObject, , , email, cc, , "You have been assigned a project by " & origin & "!", body, False
rec.MoveNext

Loop

'Clean up and releasing the recordset.
rec.Close
Set rec = Nothing

Exit_Command202_Click:
Exit Sub

Err_Command202_Click:
MsgBox Err.Description
Resume Exit_Command202_Click

End Sub

Now first email goes but on the second email the error message come "Unknown Message Recipients;the message was not sent". I think it could not refresh the veriable 'strEmailAddresses'. Could you please check. Thnx.

PHV · Sep 22, 2005

Replace this:
strEmailAddresses = strEmailAddresses & rec![Emailadd]
email = strEmailAddresses
with this:
email = rec![Emailadd]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

mauricionava · Sep 22, 2005

I tried to use the code but I dont get to send the body nor the origin of the email.

I receive the email but only with the subject without the "origin" and with no body.

How can I set it to send them?

touqir · Sep 22, 2005

Great PHV! Thank you for your kind guidance. It works. Well done. Another thing is that before sending each email, a message comes to confirm that some software is trying to send an email through your outlook. How can this message deactive? Once again many Thanks.

Mauricionava, you are right. Actually I have not posted the whole routine. For body & origion, I will post it today.

touqir · Sep 23, 2005

Hi Mauricionava, following is the body matter. Hope now ur problem soloved.

body = "Dear" & Chr(32) & rec![Name] & Chr(44)
body = body & Chr(10)
body = body & "This is to bring into your notice that below listed PurchaseOrder(s) is (are) still open for sometime now:"
body = body & Chr(10)
body = body & "PO No =" & rec![pono] & Chr(44) & "PO Date =" & rec![podate] & Chr(44) & "Vender Name =" & rec![Name]
body = body & Chr(10)
body = body & Chr(10)
body = body & "WARNING!! Please ensure that the above Purchase Order(s) is (are) closed ASAP. In case equipment against these POs is not made available deployment timings will be violated. Please update us with the latest status of the above POs."

Anybody knows that how could I change the color of a line. Actually I want that the "WARNING" message should be display in email in red color. Any help?????????????

Remou · Sep 23, 2005

Hi
You can format text using Outlook and HTML body, but not using SendObject, I think.

kphu · Sep 23, 2005

touqir said:
Another thing is that before sending each email, a message comes to confirm that some software is trying to send an email through your outlook. How can this message deactive?

This is a security feature built in by microsoft to fight viruses and other potentially malicious programs. There is a freeware program called express clickyes that can clickyes for you each time the message comes up. Its a tiny bit slow but much better then sitting there and clicking yes to every message that comes up. You can download a free copy here:

Express-clickyes

In order for the program to work you must insert some statements in your code. Here's the general code provided.

Code:

'Declare Windows' API functions
Private Declare Function RegisterWindowMessage _
        Lib "user32" Alias "RegisterWindowMessageA" _
        (ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" (ByVal lpClassName As Any, _
        ByVal lpWindowName As Any) As Long

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

Private Sub SomeProc()
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long

' Register a message to send
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)

' ...
' Do some Actions
' ...

' Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)

End Sub

I've taken the liberty of inserting your code so that it should work using what was provided above.

Code:

Private Declare Function RegisterWindowMessage _
        Lib "user32" Alias "RegisterWindowMessageA" _
        (ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" (ByVal lpClassName As Any, _
        ByVal lpWindowName As Any) As Long

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

Private Sub Command202_Click()
On Error GoTo Err_Command202_Click

  'Declaration of Variables
    Dim email As String
    Dim cc As String
    Dim origin As String
    Dim body As String
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rec As DAO.Recordset
    Dim strEmailAddresses As String
    Dim wnd As Long
    Dim uClickYes As Long
    Dim Res As Long

' Register a message to send
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)


    'Open and populate the recordset with required data
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM pomaster"

 ' Open up the recordset
   Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

 'Loop through all records in table and concatenate the e-mail addresses


 Do Until rec.EOF

 
 email = rec![Emailadd] 
DoCmd.SendObject acSendNoObject, , , email, cc, , "You have been assigned a project by " & origin & "!", body, False
 rec.MoveNext
 
 Loop

' Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)

 'Clean up and releasing the recordset.
 rec.Close
 Set rec = Nothing

Exit_Command202_Click:
    Exit Sub

Err_Command202_Click:
    MsgBox Err.Description
    Resume Exit_Command202_Click
    
End Sub

I hope this works out for you. Good luck.

Ken

Remou · Sep 23, 2005

Another way around Outlook Security, recommeded by PHV, can be found by searching for Outlook Object Model Guard. Here is one site returned by such a search:

http://www.outlookcode.com/d/sec.htm

CosmoKramer · Sep 23, 2005

Check out the Outlook Redemption section on the site that Remou just gave. I use it, and it works great...

Si hoc legere scis, nimis eruditionis habes

PHV · Sep 26, 2005

touqir, do a google search for outlook object model guard

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

touqir · Oct 17, 2005

Thnx. Everybody! Now my routine is as follows

Private Sub Activity_Dates_Click()
On Error GoTo Err_Activity_Dates_Click

'Declaration of Variables
Dim Email As String
Dim Email1 As String
Dim cc As String
Dim origin As String
Dim body As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strEmailAddresses As String
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long

'Open and populate the recordset with required data
Set dbs = CurrentDb
strSQL = "SELECT * FROM pomaster"

' Open up the recordset
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'Loop through all records in table and concatenate the e-mail addresses

Do Until rec.EOF
Email = rec!
body = "Dear" & Chr(32) & rec![Requestor] & Chr(44)
body = body & "This is to bring into your notice that below listed Order(s) still open for sometime now:"
body = body & "PO No =" & rec![pono] & Chr(44) & "PO Date =" & rec![podate] & Chr(44) & "Vender Name =" & rec![Vendor]

body = body & "WARNING!! Please update us with the latest status of the above Orders."
body = body & "P.S. kindly let us know the percentage of services or supplies. body = body & "Best Regards"
body = body & Chr(10)

DoCmd.SendObject acSendNoObject, , , Email, cc, , "Open POs" & origin & "!", body, True
rec.MoveNext

Loop

'Clean up and releasing the recordset.
rec.Close
Set rec = Nothing

Exit_Activity_Dates_Click:
Exit Sub

Err_Activity_Dates_Click:
MsgBox Err.Description
Resume Exit_Activity_Dates_Click

End Sub

This routine sends a sperate email to each of the record containg the email address but that if there are more than one record of a same email, it should be sent in a single email. In this way some receiver could a 1 record and some may have more. and record details should as follows in email body.

Do Until rec.EOF
Email = rec![Email]
body = "Dear" & Chr(32) & rec![Requestor] & Chr(44)
body = body & "This is to bring into your notice that below listed Order(s) still open for sometime now:"

*****************************************************
body = body & "PO No =" & rec![pono] & Chr(44) & "PO Date =" & rec![podate] & Chr(44) & "Vender Name =" & rec![Vendor]

In this area all the records detail should come either it is one order or more then 1 order.

*****************************************************

body = body & "WARNING!! Please update us with the latest status of the above Orders."
body = body & "P.S. kindly let us know the percentage of services or supplies. body = body & "Best Regards"
body = body & Chr(10)

Futher more I want that after sending email each record should be updated and so that next time mail could be sent only those email address which have been sent any email yet.
Could anybody help me out.

touqir · Oct 20, 2005

Hi. Everybody! Following is a routine to send the emails to email address of a table.

Private Sub Activity_Dates_Click()
On Error GoTo Err_Activity_Dates_Click

'Declaration of Variables
Dim Email As String
Dim Email1 As String
Dim cc As String
Dim origin As String
Dim body As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rec As DAO.Recordset
Dim strEmailAddresses As String
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long

'Open and populate the recordset with required data
Set dbs = CurrentDb
strSQL = "SELECT * FROM pomaster"

' Open up the recordset
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'Loop through all records in table and concatenate the e-mail addresses

Do Until rec.EOF
Email = rec!
Email1= rec![Email]
body = "Dear" & Chr(32) & rec![Requestor] & Chr(44)
body = body & "This is to bring into your notice that below listed Order(s) still open for sometime now:"

'Internal loop to collect the all records relating to a singal email address.

Do Until email = Email1
body = body & "PO No =" & rec![pono] & Chr(44) & "PO Date =" & rec![podate] & Chr(44) & "Vender Name =" & rec![Vendor]
rec.MoveNext
email = rec![email]
Loop
Exit Do

body = body & "WARNING!! Please update us with the latest status of the above Orders."
body = body & "P.S. kindly let us know the percentage of services or supplies. body = body & "Best Regards"
body = body & Chr(10)

DoCmd.SendObject acSendNoObject, , , Email, cc, , "Open POs" & origin & "!", body, false

Loop

'Clean up and releasing the recordset.
rec.Close
Set rec = Nothing

Exit_Activity_Dates_Click:
Exit Sub

Err_Activity_Dates_Click:
MsgBox Err.Description
Resume Exit_Activity_Dates_Click

End Sub

when I execute this nothing happens, no error, no results. I want that instead of sending a singal email against the each email field in the table, all relating records of a email address should be sent in a singal email. Could any body help me out. Thnx.

Remou · Oct 20, 2005

Hi Touqir
You may wish to glance at the English, when you get the code working.

Code:

'It may be best to sort by something, email perhaps.
strSQL = "SELECT * FROM pomaster ORDER BY Email, PONo"

' Open up the recordset
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'Loop through all records in table and concatenate the e-mail addresses

 Do Until rec.EOF
    'One of these is unnecessary
    'Email = rec![Email]
    Email1 = rec![Email]
    'body = "Dear" & Chr(32) & rec![Requestor] & Chr(44)
    body = "Dear " & rec![Requestor] & ", " & vbCrLf 'Easier to read
    body = body & "This is to bring into your notice that below listed "
    body = body & "Order(s) still open for sometime now:" & vbCrLf
    
    'Internal loop to collect the all records relating to a singal email address.
    
    'This [blue]Do Until Email = Email1[/blue] is one reason why nothing is happening.
    'Email already equals Email1, from above. Try:
    Do While Email1 = rec!Email
        body = body & "PO No =" & rec![pono] & ", " & "PO Date =" & rec![podate]
        body = body & ", " & "Vender Name =" & rec![Vendor] & vbCrLf
        
        rec.MoveNext
        If rec.EOF() Then Exit Do
        'I do not think this is a good idea:
        'Email = rec![Email]
    Loop
    'This is the other reason [blue]Exit Do[/blue]. You do not need an Exit Do
    'unless you need to exit unexpectedly. For example:
    'If Email = "" Then
    'Exit Do
   
    body = body & "WARNING!! Please update us with the latest status of the above Orders."
    'Typo or cut and paste error here has been corrected
    body = body & vbCrLf & "P.S. kindly let us know the percentage of services or supplies."
    body = body & vbCrLf & "Best Regards"
    'body = body & Chr(10)
    
    DoCmd.SendObject acSendNoObject, , , Email1, cc, , "Open POs" & origin & "!", body, True  ', False
 
Loop

touqir · Oct 20, 2005

Hi Remou! Thank you very for you Kind guidance. It's great. It's working perfectly. I also need a litte more help in this regard. I want to change the colour of "WARNING! " message in the above body text. Is it possible. Thnx. once again for your help.

Remou · Oct 21, 2005

Most welcome. To change text colours, you need something such as Outlook and this:
How to Email From Access using Outlook
faq702-2921
Use .HTMLBody, rather than .Body, and HTML encoding.

touqir · Oct 24, 2005

Thnx. remou for guidance but sorry I'm can't follow this. Where I have to use .HTMLBody and HTML Encoding. Would you please guide me by setting it in my code. Thnx. one again

Remou · Oct 24, 2005

You must have Outlook for this to work.

Code:

'You will need a reference to the Microsoft Outlook 9.0 Object Library
'Create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
'Use strBody for variable, rather than body, to avoid confusion.
'Ditto CC
Dim strBody, strCC

'Add a little HTML
strBody = strBody & "<STRONG><FONT color=#ff0000>WARNING!!</FONT></STRONG> "
strBody = strBody & "Please update us with the latest status of the above Orders."
strBody = strBody & vbCrLf & "P.S. kindly let us know the percentage of services or supplies."
strBody = strBody & vbCrLf & "Best Regards"

'Intead of this:
'DoCmd.SendObject acSendNoObject, , , Email1, cc, , "Open POs" & origin & "!", body, True   ', False
'Use this (from the supplied link)
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'Creates and sends email
With objEmail
    .To = Email1
    .CC = strCC
    .Subject = "Open POs" & origin & "!"
    'Use .HTMLBody instead of .Body
    .HTMLbody = strBody
    .Display 'Displays the email in Outlook.  Change to send if you want to
      'send without viewing the email.
End With

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sending emails from Accsess 2

Programmer

Technical User

Programmer

MIS

Programmer

Programmer

Programmer

Technical User

MIS

Technical User

Programmer

MIS

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor