access and outlook

[Lhuffst]

I have an access 2003 system that tracks citations from the automated cameras. When we get the notifications, we need to send an email to the driver and the driver's supervisor notifying them of the due date. Based on what I've read so far, I created a command button that takes the driver & supervisor's name entered on the form and puts it in an email. I do not receive any errors but I don't receive the email either. I believe this is because the format in outlook is different.
example.
On the form
txtdriver = "Joe Smith"
txtSuper = "Happy Harry"

In outlook
Joe Smith is really Smith, Joe and because we may have more than 1 Joe Smith, their email addresses could be slightly different.

This is what I'm using currently.
Function SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment

   
   On Error GoTo ErrorMsgs
   
 tmpto = Forms!FrmCitations!txtDriverName.Value
 tmpSuper = Forms!FrmCitations!txtEmpSupervisor
 

 
   Set objOutlook = CreateObject("Outlook.Application")               'Create the outlook session
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)             ' Create the message.
   
   With objOutlookMsg
    ' Set objOutlookRecip = .Recipients.Add("txtdrivername.value")           ' Add the To recipient(s) to the message.
    
        If IsNull(tmpto) Or Len(tmpto) = 0 Then
   
        Else
        Set objOutlookRecip = .Recipients.Add(tmpto)
        objOutlookRecip.Type = olTo
        End If
   
         
     
        If IsNull(tmpSuper) Or Len(tmpSuper) = 0 Then
            tmpSuper = "Bjackso"
        
        Else
            Set objOutlookRecip = .Recipients.Add(tmpSuper)           ' Add the CC recipient(s) to the message.
            objOutlookRecip.Type = olCC
        End If
      
     .Subject = "This is an Automation test with Microsoft Outlook"    ' Set the Subject, Body, and Importance of the message.
     .Body = "Last test." & vbCrLf & vbCrLf
     .Importance = olImportanceHigh                                    'High importance
     
    '  If Not IsMissing(AttachmentPath) Then                            ' Add attachments to the message.
    '     Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    '  End If
     
      For Each objOutlookRecip In .Recipients                            ' Resolve each Recipient's name.
         If Not objOutlookRecip.Resolve Then
            objOutlookMsg.Display
      End If
      Next
    '  .Send
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set objOutlookRecip = Nothing
   Set objOutlookAttach = Nothing
   Exit Function
   
ErrorMsgs:
   If Err.Number = "287" Then
    MsgBox "You clicked No to the Outlook security warning. " & _
           "Rerun the procedure and click Yes to access e-mail" & _
           "addresses to send your message. For more information, " & _
           "see the document at [URL unfurl="true"]http://www.microsoft.com/office"[/URL] & _
           "/previous/outlook/downloads/security.asp. "
   ElseIf Err.Number = 13 Then
    Exit Function
   Else
          MsgBox Err.Number, Err.Description
   End If
End Function

Is there a way to invoke outlook when they enter the txtdriver box and txtSuper box to select the names properly?

One other thing, even though the code above works, I continually received a type mismatch error (13) and couldn't figure out why so I trapped the error. The email still created correctly -- well except for the name formatting part. I blocked the .send for now until I get everything working properly which makes the email popup and when I change the names to the correct format and send, I receive the email.

I have seen other scenarios but none that really fit what I"m trying to do so I am looking for any suggestions.
Thanks
lhuffst

 

[timfoster]

Your current solution is dependent on Outlook being installed and a current profile being accessible. You'd be better using CDO for this to access the SMTP server directly. This will get rid of the security warning as you're no longer using Outlook (which is what produces the security warning).

Google CDO + VBA and you'll get shed loads of stuff.

 

[Lhuffst]

Great. thanks, I'll look into this.

 

[newestAF]

hey there Lhuffst. First thing I noticed was in your first If statement. There's nothing after Then. Did you forget to post it or is it actually missing? Also, remove the () after the IsNull but before the Len statements. I notice most of my errors are caused by little things like that.