Access Email

shieldsco
shieldsco used Ask the Experts™
on
I'm using the following coded to generated Outlook emails from Access. The output is in the format :
Emily.Rosenb@abc.gov#mailto:Emily.Rosenb@abc
How would I remove the #mailto:Emily.Rosenb@abc?





On Error GoTo err
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strBody As String
    Dim db As Database
    Dim rs As Recordset
    Dim emailcount As Long
    
    emailcount = 0
    
    Set OutApp = CreateObject("Outlook.Application")
    Set db = CurrentDb

    Select Case Me.EMAILTYPE
        Case "Annual Training"
            Set rs = Forms!frmAnnualTraining.Form.RecordsetClone
    End Select

    If rs.RecordCount = 0 Then
        MsgBox "There are no reminders to create!", vbCritical, "Reminders"
        Set rs = Nothing
        Exit Sub
    End If
            
    rs.MoveFirst
    Do Until rs.EOF
        If Not IsNull(rs![EMPEMAIL]) And IsNull(rs![AETDATE]) Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = rs![EMPEMAIL]
                .CC = ""
                .BCC = ""
                .Subject = Me.EMAILSUBJECT
                .HTMLBody = Me.EMAILDESC
                .Save
                emailcount = emailcount + 1
            End With
        End If
        rs.MoveNext
    Loop

If emailcount = 0 Then MsgBox "There are no employees without an Annual Training date for the calendar year." Else MsgBox "Created Alerts.  Please review them and send."
DoCmd.Close acForm, "frmReminders"
Exit Sub

err:
MsgBox "Unable to create emails at this time.  If problem persists, contact system administrator."
MsgBox err.Description
Exit Sub
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try using Recipients.Add instead of directly setting the To method:

With OutMail
  .To = .Recipients.Add(rs("EMPEMAIL")
  etc

See this link: https://docs.microsoft.com/en-us/office/vba/api/outlook.recipients.add

If that doesn't work, you can do this:

.To = Split(rs![EMPEMAIL], "#")(0)

Author

Commented:
Thanks .... I found a solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial