mrl1021
asked on
Fix Email Automation Programming
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("C H 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
Private Sub Command60_Click()
Dim rs As DAO.Recordset, FirstLastName As String, CommaLocation As Long
Dim SendToAddresses As String
Set rs = CurrentDb.OpenRecordset("C
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SendToAddresses = SendToAddresses & FirstLastName & IIf(Nz(rs![HomeEmail], "") = "", "", rs![HomeEmail] & ";") & IIf(Nz(rs![FHEmail], "") = "", "", rs![FHEmail] & ";")