Access Email

shieldsco used Ask the Experts™
I'm using the following coded to generated Outlook emails from Access. The output is in the format :
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
    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
                emailcount = emailcount + 1
            End With
        End If

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

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

Watch Question

Do more with

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

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

See this link:

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

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


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