Customize "Save As Outlook Contact" Macro Command

[EternalMyrtle]

Hello,

My database has a button that saves a contact as an Outlook contact. This is a standard button that came with a template that I used when I was getting started several years ago. When clicked it runs a macro (RunCommand > SaveAsOutlookContact).

It is a great feature as is but it is not robust enough as I need it to copy additional info from junction tables. So, I would like to customize this using VBA.

Does anyone know if this is possible or how to do this??

Thank you!

 

[EternalMyrtle]

Sorry, once again I should have searched harder before posting

I found this code:

Function AddOlContact()
On Error GoTo Error_Handler
  Const olContactItem = 2
  Dim olApp As Object
  Dim Ctct As Object
 
  Set olApp = CreateObject("Outlook.Application")
  Set olContact = olApp.CreateItem(olContactItem)
 
  With olContact
    .FirstName = "Daniel"
    .LastName = "Alba"
    .JobTitle = ""
    .CompanyName = "MINI CARDA"
    .BusinessAddressStreet = "22 ClearPoint"
    .BusinessAddressCity = "Pointe-Claire"
    .BusinessAddressState = "Quebec"
    .BusinessAddressCountry = "Canada"
    .BusinessAddressPostalCode = "H9X 3A6"
    .BusinessTelephoneNumber = "(514) 488-0956"
    .BusinessFaxNumber = ""
    .Email1Address = "mini@mini.com"
    .MobileTelephoneNumber = ""
    .Save 'use .Display if you wish the user to see the contact pop-up
  End With
 
Error_Handler_Exit:
  On Error Resume Next
  Set olContact = Nothing
  Set olApp = Nothing
  Exit Function
 
Error_Handler:
  MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
  Err.Number & vbCrLf & "Error Source: AddOlContact" & vbCrLf & "Error Description: " & _
  Err.Description, vbCritical, "An Error has Occured!"
  Resume Error_Handler_Exit
End Function

Hopefully I will be able to modify it to suit my needs (using Screen.ActiveForm.ControlName) but some of the info is not in the main form but in subforms or even other forms in case of company addresses...

 

[EternalMyrtle]

So far so good but I am having one irritating problem. Email addresses are copying like this:

EmailAddress@Blah.com#mailto:EmailAddress@Blah.com#

When you try to save the contact in outlook you get an error because of this. I tried creating a text only email address field and populating it with data from the main field but it still shows up with the #mailto yet it doesn't look like this in the form or in the table

 

[pr2-eugin]

I am not completely sure where this is happening. But just before you save depending on the data you need you can use the Mid function?

? Mid("EmailAddress@Blah.com#mailto:EmailAddress@Blah.com#", 1, Instr("EmailAddress@Blah.com#mailto:EmailAddress@Blah.com#", "#")-1)
EmailAddress@Blah.com

This way you can separate the email from the funny string?

 

[EternalMyrtle]

It almost worked. Did this

.Email1Address = Mid(Screen.ActiveForm.EmailAddress, 1, InStr("EmailAddress@Blah.com#mailto:EmailAddress@Blah.com#", "#") - 1)

Now the @ is getting cut off like this:

EmailAddress@abc

but no more #maito...

 

[EternalMyrtle]

Oops I think I see the problem obviously had to revise the InStr part.... ha ha!

 

[EternalMyrtle]

It's perfect thank you Paul!!

Here is the code

.Email1Address = Mid(Screen.ActiveForm.EmailAddress, 1, InStr(Screen.ActiveForm.EmailAddress, "#") - 1)

 

[pr2-eugin]

Oops I think I see the problem obviously had to revise the InStr part.... ha ha!

Ha Ha ! Glad you found it on your own ! Good luck EternalMyrtle !

 

[EternalMyrtle]

In case anyone else needs to use this code... if any of the fields are null you will get an error I am putting zeros in null fields

 

[pr2-eugin]

I am putting zeros in null fields

or better using vbNullString

 

[EternalMyrtle]

or better using vbNullString

How do I use that?? Yes that sounds better unless I want zeros all over the place!!

 

[pr2-eugin]

I think the error occurs when you are referring to the ActiveControl? If so try..

.Email1Address = Mid([COLOR=Red][B]Nz([/B][/COLOR]Screen.ActiveForm.EmailAddress[COLOR=Red][B], "#")[/B][/COLOR], _
                1, InStr([COLOR=Red][B]Nz([/B][/COLOR]Screen.ActiveForm.EmailAddress[COLOR=Red][B], "#")[/B][/COLOR], "#") - 1)

 

[EternalMyrtle]

Always so much more to learn... Thanks again!

 

[pr2-eugin]

No problem !