fix code to send email (Access97)

Topic

New Reply

Can anyone tell or refer me. I’ve gotten this far with modifying code to generate and send an email from access.
How could I fix this code to make it loop through all the records in the form/recordset that’s open… and how can I fix the null error msg when someone doesn’t have an email address? ( i thought i was telling access to look at another table and grab the fax number instead) (i use microsoft fax to send faxes via outlook 97 with win95 as the OS). Thanks.

Option Compare Database
Option Explicit

Private Sub Command26_Click()

Dim appOutlook As New Outlook.Application
Dim lst As Access.TextBox
Dim strsql As String
Dim msg As Outlook.MailItem
Dim strBody As String
Dim strEMailRecipient
Dim strSubject As String
Dim strTo As String

On Error GoTo ErrorHandler

Set lst = Me![txtEmail]

If IsNull(lst) Then
lst.ControlSource = strsql
strsql = “SELECT tblDrPhone.DrPhone FROM tblDrPhone WHERE tblDrPhone.PhoneType = Like ‘ *fax*’ ”

End If

strEMailRecipient = lst.Value
strSubject = Me![txtSubject].Value
strBody = Me![txtBody].Value

Set msg = appOutlook.CreateItem(olMailItem)

With msg

.To = strEMailRecipient
.Subject = strSubject
.Body = strBody
.ReadReceiptRequested = True
.Display
‘.Send

End With

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

Resume ErrorHandlerExit

End Sub

Reply | Quote

Replies

To be able to send the message to lots of people, you need to create a recordset in code and loop through the recordset. So the email address does not come from the form, instead it comes from the recordset.
If people don’t have an email address (or it is not reasonably structured) then you don’t want to send the message. Your code sets up strsql in this case, but then goes on and tries to send the email. I haven’t tried to send faxes, but I imagine it uses different code from that for sending email. You need an if… then ..else.. end if so it can choose which bit of code to use.
Here is some code that uses a form to set up the subject and message, but gets the email address from a recordset.
I uses a function fnvalidatemeail that checks that the address meets requirements such as no spaces, one @ etc.

Private Sub cmdSendOutlook_Click()
On Error GoTo Err_handler
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Set db = CurrentDb
    
    If IsNull(Me!Subject) Then
        MsgBox ("A subject is required")
        Me!Subject.SetFocus
    Exit Sub
    End If
 
   If IsNull(Me!Message) Then
  	MsgBox ("A message is required")
  	Me!Message.SetFocus
  	Exit Sub
   End If
 
 
    sql = "SELECT tblContacts.*"
 							   
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    Dim Outlk As Outlook.Application
    Dim oNameSpace As Outlook.NameSpace
    Dim oMailItem As Outlook.MailItem
    
    
    Set Outlk = New Outlook.Application
    Set oNameSpace = Outlk.GetNamespace("MAPI")
    Set oMailItem = Outlk.CreateItem(olMailItem)
  
    Dim stEmailString As String
    Dim stSubject As String
    Dim stText As String
  
    stSubject = Me!Subject
    stText = Me!Message
  
    ' now create a message for each person that has a valid email address
    rs.MoveFirst
    Do While Not rs.EOF
   
        If Not IsNull(rs!EmailAddress) Then
            If fnValidateEmail(rs!EmailAddress) Then
                sEmailString = rs!EmailAddress
                With oMailItem
                    .To = sEmailString
                    .Subject = stSubject
                    .Body = stText
                    .Send
                End With
               
            End If
        End If
  
    	rs.MoveNext
    	Set oMailItem = Outlk.CreateItem(olMailItem)
    Loop
 
   
   

   Set Outlk = Nothing
   Set oNameSpace = Nothing
   Set oMailItem = Nothing
Exit_cmdSendOutlook_Click:
   Exit Sub

Err_handler:
   MsgBox Err.Description & " Number " & Err.Number
   
   Resume Exit_cmdSendOutlook_Click
   

End Sub

I don’t use Outlook much, so I am not sure about the Namespace stuff. What I have above is a (modified) version of something that works, but maybe you could take out the namespace stuff.

Reply | Quote

Before I say anything, John’s idea is a good one, just maybe not fast enough.

I have done this kind of thing with a loop thru a recordset, but used SendObject instead, I’m not advocating SendObject here.

What I did notice was that it would take quite a long time to send emails, so if you are sending the same subject and message text then I would concatenate the email addresses in one string and send the one email at the end of the recordset. You may have to be careful with the number of email addresses in the text string.

Pat

Reply | Quote

Thanks Pat and John… let me clarify alittle more.

one email, one name… i used to string together the email addresses but cannot do so anymore… everyone views the TO: field and there is poor error handling when using the BCC: field (see my post in the outlook forum). therefore… the form that i am using to create the msg is really several fields concatenated together to create a document as the body of the email… I cannot use send object as several of the recipients REFUSE to open attachments… they are used to recieving the text and they vehemently resist changing that… fine… so… each record in the form is a unique email made specifically for an individual… there is some repitition of subjects and msg bodies and even recipients but these really can’t be grouped in any way.

currently, i have to press the “send email” button that runs this code, on each and every record in the recordset. I would like it to loop through each record on the form (by itself).. just as if I was pressing a navigation button then reclick the send email button til it gets to the end… (IS THERE A WAY TO LOOP THROUGH THE DATASHEET OF THE FORM AS OPPOSED TO THE UNDERLYING QUERY SEEING AS THERE ARE SEVERAL CALCULATED CONTROLS ON THE FORM THAT NEED TO BE INCLUDED IN THE MSG TEXT)

now i understand my thought process may be wrong here… which is why i’m reaching out for advice.

REGARDING THE FAX: the way outlook and microsoft fax work together is that if I put a number in the TO: field of a msg… outlook “knows” that it’s a fax. I send groups of emails regularly, some have email addresses, some fax numbers – outlook does it’s job of resolving which is which and sending appropriately. so my question is really about what the best method is for getting the number. the current recordset for the form’s underlying query does not include the fax numbers… so i only need to pull it from another table, (the link is the field DrID), when email is empty.

But as I’m thinking more about this… i could probably go a different route with the faxes… they can be grouped by recipient with the msgs being sent as attachments. In this case… each msg for the one recipient would have to be constructed, attached to an email and sent to the respective fax number. not sure how to accomplish that but i welcome comments and suggestions.

Reply | Quote

<>
You don’t need to send an attachment using SendObject, simply define the object type as acSendNoObject.

I have no experience with sending faxes so I cannot help you there.

Pat

Reply | Quote

So you want a different subjct and message for each person, and these are on the form.
Here is some code that loops through the records in a form.

On Error GoTo Err_handler
 
   Dim Outlk As Outlook.Application
   Dim oNameSpace As Outlook.NameSpace
   Dim oMailItem As Outlook.MailItem
    
   Set Outlk = New Outlook.Application
   Set oNameSpace = Outlk.GetNamespace("MAPI")
   Set oMailItem = Outlk.CreateItem(olMailItem)
 
   Dim stEmailString As String
   Dim stSubject As String
   Dim stText As String
   DoCmd.GoToRecord acActiveDataObject, , acFirst
   
   Do While Not IsNull(Me!Messageid)
   stSubject = Me!Subject
   stText = Me!Message
   
       If Not IsNull(Me!EmailAddress) Then
   
       stEmailString = Me!EmailAddress
       With oMailItem
         .To = stEmailString
         .Subject = stSubject
         .Body = stText
         .Send
       End With
      
    End If
      
        DoCmd.GoToRecord acActiveDataObject, , acNext
       
       Set oMailItem = Outlk.CreateItem(olMailItem)
   Loop

  
  
  
Exit_cmdSendOutlook_Click:
  Set Outlk = Nothing
  Set oNameSpace = Nothing
  Set oMailItem = Nothing

  Exit Sub
Err_handler:
  MsgBox Err.Description & " Number " & Err.Number
  
  Resume Exit_cmdSendOutlook_Click
  
End Sub

The issue I wasn’t sure about was how to stop the loop. I have given each message a messageID and the blank record at the end has a null messageID and this stops the loop.

About faxing – if all you need is a fax number then can you incorporate that number into the underlying query and display it on the form.
If you can’t do that, then you could use a dlookup to find the right fax number.
Your previous code :

strsql = "SELECT tblDrPhone.DrPhone FROM tblDrPhone WHERE tblDrPhone.PhoneType = Like ' *fax*' "

seems to find all fax numbers rather than the specific one.
Even then strsql is just a string, so setting something to equal this is not getting a fax number.

Reply | Quote

Jumping in late but here is my input. I agree with Pat, the sendobject command is what I use for bulk emails.

>>one email, one name

Not a problem with the SendObject method, as part of your loop through the recordset send the email.

>>IS THERE A WAY TO LOOP THROUGH THE DATASHEET OF THE FORM AS OPPOSED TO THE UNDERLYING QUERY SEEING AS THERE ARE SEVERAL CALCULATED CONTROLS ON THE FORM THAT NEED TO BE INCLUDED IN THE MSG TEXT)

You really need to use the recordset clone and recalculate the required fields. You could move to the next record in the form and read from the fields but that will be slower than retrieving the data from the recordsetclone and calculating in code.

>>now i understand my thought process may be wrong here… which is why i’m reaching out for advice.
Thought processes are unique and that is why this forum with so many different viewpoints and approaches is such a valuable resource.

I also know nothing about Fax so you are the expert and I’ve learned something. However check for null or empty name details and if so create another recordset returning the fax number.

I’ve not tested the following so it may have a couple of typo’s or logic problems

Stewart

Private Sub butDistribute_Click()

On Error GoTo butDistribute_Click_ERR
Dim rstMyForm as recordset
Dim rstFax as recordset
Dim MailList As String
Dim MsgBody as String

set rstMyForm = me.recordsetclone ‘ get the data from the form that you are using.

If rst.RecordCount = 0 Then
MsgBox “there is no data for email messages”, vbInformation, “Mail Not Sent”
Else

rst.MoveFirst

Do Until rstMyForm.EOF ‘ loop through recordset building the email and sending for each record.
‘ get address data
if not isnull(rstMyForm!Lastname) then
MailList = rstMyForm!Lastname & ” ” & rst!FirstName
else
MailList = Null
set rstFax = currentdb().openrecordset(“Select FAXNO from tblFax where DrID = ” & rstMyForm!drID)
if rstFax.recordcount > 0 then MailList = rstFax!FAXNO
end if

‘ check that there is a recipient. if there is proceed, else you could write to a log for incomplete data or something.
if not isnull(MailList) then ‘there is actually a recipient so continue
MsgBody = rstMyForm!field1 & ” ” & rstMyForm!field2 ‘building the msg body
MsgBody = MsgBody & vbcrlf ‘Add a line
MsgBody = MsgBody & (rst!field5 * 17)/field6 ‘ add your calculated fields
‘ continue building your msg body. I’ve sent some fairly long messages this way and am not aware of any limitation
‘ not to say that there isn’t one. Anything you can calculate on a form you can calculate in code and add to the message.

‘ Now send the particular email.
‘ in the next line you are sending no object just the email. You have set the to field, the mail subject and the
‘ msgbody that you have built on the way. The False on the end says don’t edit the message, just send automatically.

Docmd.SendObject acSendNoObject ,,,MailList,,,”Enter Your Email Subject Here”,MsgBody,False
else
‘ write your log file here or notify user of failure if that is what you want.
end if
rst.MoveNext ‘ get to the next record.
Loop

End If
rstMyForm.close
db.close

butDistribute_Click_Exit:
Exit Sub

butDistribute_Click_ERR:
Select Case Err
Case Else
MsgBox Error, vbInformation, Err
End Select
Resume butDistribute_Click_Exit:
End Sub

Reply | Quote

Thank you all for the input… in the end, a combination of the two approaches worked… except for the darn error #2105 msg at the end of the recordset – for now, i simply addressed that error specifically. I have not tried Jim’s suggestion yet but will look into it… This db is coded to my particular computer setup and the day will come that i will have to change my system… I like the integration of microsoft fax and outlook… but it’s not used much anymore… so finding other “options” for handling faxes is necessary, perhaps the tried and true print it and fax it yourself method??? Anyhow…

here’s the code used for sending the emails:

Private Sub Command26_Click()

Dim appOutlook As New Outlook.Application
Dim lst As Access.TextBox
Dim strsql As String
Dim msg As Outlook.MailItem
Dim strBody As String
Dim strEMailRecipient
Dim strSubject As String
Dim strTo As String
Dim rstMyForm As dao.Recordset

On Error GoTo ErrorHandler

Set rstMyForm = Forms!qryEmailApprovalsOver500.RecordsetClone

If rstMyForm.RecordCount = 0 Then
MsgBox “There are no approvals to send”
Else
DoCmd.GoToRecord acActiveDataObject, , acFirst

End If
Do Until rstMyForm.EOF

Set lst = Me.[txtEmail]

strEMailRecipient = lst.Value
strSubject = Me.[txtSubject].Value
strBody = Me.[txtBody].Value

Set msg = appOutlook.CreateItem(olMailItem)

With msg
.To = strEMailRecipient
.Subject = strSubject
.Body = strBody
.ReadReceiptRequested = True
.Display
.Send
End With

DoCmd.GoToRecord acActiveDataObject, , acNext

Loop

ErrorHandlerExit:

Exit Sub

ErrorHandler:
If Err.Number = 2105 Then
MsgBox “You are finished sending approval emails”, vbOKOnly, “Sending Complete”
Resume ErrorHandlerExit

Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

Resume ErrorHandlerExit
End If
rstMyForm.Close

End Sub

And Stewart, you are right on about the combo of experiences and techniques, the lounge provides me so many ideas and solutions. It is truly a valuable resource.

P.S. what’s the difference between Do Until… Loop and Do…Loop Until ???

Reply | Quote

The difference between

Do Until condition
Instructions go here
Loop

and

Do
Instructions go here
Loop Until condition

is probably most clear if you imagine the extreme situation that condition is already True when Do … Loop is entered. The instructions in the first Do … Loop will not be executed at all, because the terminating condition is tested at the start of the block. The instructions in the second Do … Loop will be executed once, then the condition is tested.

Reply | Quote

can this be done in access 2k?

Reply | Quote

I recently implemented sending e-mail through Access without using Outlook at all. I used a FREE dll from Persists Software (AspEMail 4.5) aspemail.com. This has made things so easy compared to dealing with the Outlook object library. After I set a reference in my db to the aspmail dll, I use the following Sub to send e-mail.

Public Sub SendEMail(SendMailTo As String, SendSubject As String, SendBody As String, Optional SendAttachment As String)
Dim Mail As MailSender

‘Create an instance of the AspEmail object as follows:
Set Mail = New MailSender

‘To send email messages, AspEmail “talks” to an SMTP server. You must specify the SMTP host address and, optionally, port number as follows:

Mail.Host = “mail.yourhostname.com”
Mail.Port = 25 ‘ Optional. Port is 25 by default

‘You must also specify the sender’s email address and, optionally, name as follows:
Mail.From = “Your E-Mail goes here”
Mail.FromName = “Your Name goes here”

‘ Optional
‘To add the message recipients, CCs, BCCs, and Reply-To’s, use the AddAddress,
‘AddCC, AddBcc and AddReplyTo methods, respectively. These methods accept
‘two parameters: the email address and, optionally, name.
‘Notice that you must not use an ‘=’ sign to pass values to the methods.
‘For example,

Mail.AddAddress SendMailTo
‘I don’t use the CC and BCC so I have NOT included them in the parameters of the sub
‘Mail.AddCC “bjohnson@company2.com” ‘ Name is optional
‘Mail.AddBcc “raymond7@graytonhouse.com”
‘Mail.AddBcc “jimalmaguer@yahoo.com”

Mail.Subject = SendSubject
Mail.Body = SendBody
If Len(SendAttachment & “”) 0 Then
Mail.AddAttachment (SendAttachment)
End If

Mail.Send

End Sub

I use the above sub from whichever form I want to send e-mail from and either fill in the parameters from a recordset or from the values on the form I’m using. To use the attachment you will need another dll from Persists for attachments.

Reply | Quote