?
Solved

Fix Email Automation Programming

Posted on 2006-04-02
3
Medium Priority
?
301 Views
Last Modified: 2012-06-21
Please translate the code below. I need to make changes in it. The code is pulling email adresses from two columns: FHEmail and HomeEmail. The FHEmail data was stored as lastname, firstname but we have changed it to the traditional ____@___.___  I need to change the code so it can place these emails in the to box of Outlook like it does the HomeEmail which is in that format already.

Private Sub Command60_Click()
Dim rs As DAO.Recordset, FirstLastName As String, CommaLocation As Long

Dim SendToAddresses As String

Set rs = CurrentDb.OpenRecordset("CH Query")  'create an internal set of the record of your table

Do Until rs.EOF     'do this until the End Of File
    If Nz(rs![FHEmail], "") <> "" Then
        'Find comma, remove it, reverse first and last names:
        CommaLocation = InStr(rs![FHEmail], ",")
        FirstLastName = Mid(rs![FHEmail], CommaLocation + 2) & " " & Left(rs![FHEmail], CommaLocation - 1) & ";"
    Else
        FirstLastName = ""
    End If
    SendToAddresses = SendToAddresses & FirstLastName & IIf(Nz(rs![HomeEmail], "") = "", "", rs![HomeEmail] & ";")
    rs.MoveNext       'Move to the next record
Loop
SendToAddresses = Left(SendToAddresses, Len(SendToAddresses) - 1) 'remove last semicolon

'This line will allow you edit the message with all the emails in the to box
DoCmd.SendObject , , , SendToAddresses

End Sub
0
Comment
Question by:mrl1021
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 16357204
Try this modification... Just adding  FHEmail to the SendToAddress string:

    SendToAddresses = SendToAddresses & FirstLastName & IIf(Nz(rs![HomeEmail], "") = "", "", rs![HomeEmail] & ";") & IIf(Nz(rs![FHEmail], "") = "", "", rs![FHEmail] & ";")
0
 
LVL 8

Accepted Solution

by:
infolurk earned 2000 total points
ID: 16357207
Try

Private Sub Command60_Click()
Dim rs As DAO.Recordset
Dim SendToAddresses As String

Set rs = CurrentDb.OpenRecordset("CH Query")  'create an internal set of the record of your table

Do Until rs.EOF     'do this until the End Of File
    SendToAddresses = SendToAddresses & IIf(Nz(rs![FHEmail], "") = "", "", rs![FHEmail] & ";") & IIf(Nz(rs![HomeEmail], "") = "", "", rs![HomeEmail] & ";")
    rs.MoveNext       'Move to the next record
Loop
SendToAddresses = Left(SendToAddresses, Len(SendToAddresses) - 1) 'remove last semicolon

'This line will allow you edit the message with all the emails in the to box
DoCmd.SendObject , , , SendToAddresses

End Sub

If  FHEmail is in the same format as HomeEmail it should work.

Cheers
Steve
0
 
LVL 8

Expert Comment

by:infolurk
ID: 16357211
You can delete the string manupulation code as you shouldnt need it any more.
I.E this part
If Nz(rs![FHEmail], "") <> "" Then
        'Find comma, remove it, reverse first and last names:
        CommaLocation = InStr(rs![FHEmail], ",")
        FirstLastName = Mid(rs![FHEmail], CommaLocation + 2) & " " & Left(rs![FHEmail], CommaLocation - 1) & ";"
    Else
        FirstLastName = ""
    End If
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question