Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fix code to send email (Access97)

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: fix code to send email (Access97)

    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.
    <pre>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
    </pre>


    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.
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: fix code to send email (Access97)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fix code to send email (Access97)

    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.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: fix code to send email (Access97)

    <<I cannot use send object as several of the recipients REFUSE to open attachments>>
    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: fix code to send email (Access97)

    can this be done in access 2k?
    Kevin

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: fix code to send email (Access97)

    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.
    <pre>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
    </pre>


    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 :
    <pre>strsql = "SELECT tblDrPhone.DrPhone FROM tblDrPhone WHERE tblDrPhone.PhoneType = Like ' *fax*' "</pre>

    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.
    Regards
    John



  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Lakes State, Michigan, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fix code to send email (Access97)

    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.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fix code to send email (Access97)

    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

  10. #10
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fix code to send email (Access97)

    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 ???

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fix code to send email (Access97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •