Email and Outlook (1 Viewer)

[Douglas Post]

My goal is to send Email thru Outlook from MS Access utilizing certain fields in my form. I am testing the concept with the following code:

Private Sub Send_Info_Click()
   
  Dim Email As String
   
  '**create variables for Outlook
  Dim objOutlook As Object
  Dim objEmail As Object
   
  '**gathers information from your form.  this sets the string variable to your fields
  Email = Me!Email
   
  '***creates an instance of Outlook
  Set objOutlook = CreateObject("Outlook.Application")
  Set objEmail = objOutlook.CreateItem(olMailItem)
   
  '***creates and sends email
  With objEmail
      .To = Email
      .Body = "TESTING INSERTING TEXT IN BODY OF EMAIL"
      .Display 'SEND sends the email in Outlook.  Change to DISPLAY if you want to be able to modify or see what you have created before sending the email
  End With
   
  '**Closes Outlook
  '**objOutlook.Quit
  '**Set objEmail = Nothing
   
  Exit Sub
  '****end code****
  End Sub

The issue I am running into is that the Email field is saves as a Data Type "Hyperlink". I have done this because sometimes I just want to send an email easily without additional text. It causes a problem with the above code as my email looks like this in the (.To) field once it opens up the mail client which obviously does not work.

someemail@yahoo.com#mailto:someemail@yahoo.com#

Is there a way to truncate it to remove everything between the # marks?

Also my auto signature does not appear. Is there a switch for this? It works when I use FollowHyperlink.

 

[Kiwiman]

Howzit

Sorry I don't know a lot about the outlook library (so I can't help with the signature part), but to extract the email address you can try something like

'Get everything after first #
Email = mid(Me!Email,InStr(1, Me!Email, "#")+1)
'Remove the last #
Email = left(Email,InStr(1, Email, "#")-1)

 

[Douglas Post]

Cheers Kiwiman!

The code works but I also had to remove the "mailto:" so I modified the code as follows:

'Get everything after first :
Email = mid(Me!Email,InStr(1, Me!Email, ":")+1)
'Remove the last #
Email = left(Email,InStr(1, Email, "#")-1)

Should I be saving emails as Hyperlinks or just plain text?

 

[Kiwiman]

Howzit

Nice one. I think it is a personal preference and what you want to do with the email address. I alternate myself - but don't have any real reason why I do so. I just like trying new ways of doing the same thing.

This would also work based on your example.

Email = left(Me!Email,InStr(1, Me!Email, "#")-1)

 

[Kiwiman]

Howzit

You may want to look at this post re adding a signature

http://www.access-programmers.co.uk/forums/showthread.php?t=145301

 

[Douglas Post]

Cheers Big Ears.

That is exactly what I was looking for.

 

[vbaInet]

You can also do:

[COLOR=Red]strEmail[/COLOR] = replace(mid([Email], instr(1,[Email], ":") + 1), "#", "")

Don't call the name of your variable the same name as the field.

Re signature, have a look here:

http://www.rondebruin.nl/mail/folder3/signature.htm

Edit: late post

 

[Danick]

Hello,
I found this old post in placing a button on a form to send a single email to the person in the form and it works great for when the email field has something in it. But I get a run-time error 94 when the field is empty. I've tried various code using If Not IsNull and If IsNull but can't get it working.

Anyone have a simple fix for this? Thanks

 

[Douglas Post]

I had the same error. I just placed my email in there and it works fine. I then delete the email prior to sending. I think the problem stems from the fact the code is expecting an email to be there.

 

[Danick]

Yes I too just add a fictitious email. Actually, I only put the letter "a" and that will work as well.

I was just hoping to find a better way with error checking. I have some co-workers who like my database and want to use it for themselves. So now I have to think about how to make it work for others so they don't get the run-time errors.

BTW -thanks for your code - works really well for me.
I've got another post in this forum for a similar issue but trying to populate a bunch of emails from a filtered form. Haven't got that working yet but I'm not giving up yet...

 

[Danick]

I figured it out!! This seems to be working for me now. Thought I'd post it for anyone else who was having this same issue.

Thanks again...

Private Sub cmdSendEmail_Click()

If Not IsNull(email) Then


   Dim strEmail As String
  '**create variables for Outlook
  Dim objOutlook As Object
  Dim objEmail As Object
   
  '**gathers information from your form.  this sets the string variable to your fields
  strEmail = Me!email
   
  '***creates an instance of Outlook
  Set objOutlook = CreateObject("Outlook.Application")
  Set objEmail = objOutlook.CreateItem(olMailItem)
   
  '***creates and sends email
  With objEmail
      .To = email
      '.Body = "TESTING INSERTING TEXT IN BODY OF EMAIL"
      .Display 'SEND sends the email in Outlook.  Change to DISPLAY if you want to be able to modify or see what you have created before sending the email
  End With
   
  Exit Sub
End If
MsgBox "No email address to send to"
Exit Sub

  '****end code****
End Sub

 

[Douglas Post]

I haven't looked at this in a long time but I think you need to have some error trap coding which basically says, if no email in CC field then run a code where the inclusion of CC in the code is eliminated.