Save contact from excel userform to outlook contacts and send email from excel

[Cuba64]

Hi,

I have built a basic helpdesk in excel for jobs my company does for its clients. It has got more and more complicated and I am having to learn more about VBA. Since I started using it I have managed to capture more jobs than before therefore we can charge our clients more, now I have been asked to send clients confirmations of jobs being logged.

I have already got a reported by Textbox on my userform, I have added Telephone Number and Email address Textboxes with add contact and send confirmation command buttons.

I would like to add a new contact to outlook using reported by(Name), Telephone number and email address textboxes when I click add.

I would also like to send a basic email to the person with the data from a few other textboxes when the send button is clicked.

Rather than actually saving the contact or sending the mail, I would like it to stop at the save contact to address book and send the mail message. just get to the point where I can click save or send.

I also have to consider the different operating systems and versions of office.

I personally use Win 8 64 with Office13 64. My work computer(s) are Vista 32 with office07 - I don't use this for much as it is heavily restricted. My preferred work computer is XP 32 with Office03 so I would really like what ever vba code I use to work on any of these OS and Office versions.

Sorry if these questions have already been asked... please kindly direct me

Regards,

Cuba64

 

[Cuba64]

I have the save contact to outlook part half sorted...

Sub Create_Outlook_Contact()
Dim outlookApp As Object
Dim outlookNameSpace As Object
Dim outlookFolder As Object
Dim outookContact As Object
Dim ContactName As String
Dim ContactAddress As String
Dim ContactHomePhoneNumber As String
Dim ContactEmail As String
Set outlookApp = CreateObject("Outlook.Application")
Set outlookNameSpace = outlookApp.GetNamespace("MAPI")
outlookNameSpace.GetDefaultFolder(10).Display 'Contact won't appear without this line of code
Set outookContact = outlookApp.CreateItem(2)
ContactName = Cells(Application.ActiveCell.Row, 5).Value
ContactWorkPhoneNumber = Cells(Application.ActiveCell.Row, 6).Value
ContactEmail = Cells(Application.ActiveCell.Row, 7).Value
With outookContact
.FullName = ContactName
.HomeAddress = "Company"
.HomeTelephoneNumber = ContactHomePhoneNumber
.Email1Address = ContactEmail
.Save
End With
MsgBox "Contact Saved"
End Sub

How can I set the cells for the contact name, telephone no. and email to be from the last row?

At the moment it just selects cells from the active cell row

 

[Cuba64]

OK, I've sorted the Save contact by getting the info from the userform directly... Now in need to sort out the email issue.

 

[s.ridd]

You can add the following code into your existing procedure if suitable to do so or take across the code for outlookApp and ContactEmail into a separate procedure.

Dim outlookMailItem As ObjectSet outlookMailItem = outlookApp.CreateItem(0)
With outlookMailItem
    .To = ContactEmail
    '.Subject = "data from relevant cell/textbox"
    '.Body = "data from relevant cell/textbox"
    .Display
End With

Amend .Subject and .Body as required

Simon

 

[jsotola]

here is code that adds entry into the address book without opening outlook

the commented out code displays all the folder names in the address book and the content
left there for reference

Sub testEmail()


    Dim olk As Outlook.Application
    Dim oAl As Outlook.AddressList
    Dim i As Integer, j As Long
    
    Set olk = New Outlook.Application
    
    On Error Resume Next
     
' debug.print outputs to the "Immediate" window
' press CTRL-G if "Immediate" window is not visible
' or go to "view" tab and click "immediate window"
 
'    For i = 1 To olk.Session.AddressLists.Count
'        Debug.Print i & vbTab & olk.Session.AddressLists(i).Name & vbTab;
'        Debug.Print i & vbTab & olk.Session.AddressLists(i).AddressEntries.Count;
'        Debug.Print
'
'        For j = 1 To olk.Session.AddressLists(i).AddressEntries.Count - 1
'            Debug.Print vbTab & olk.Session.AddressLists(i).AddressEntries(j).Name
'            DoEvents
'        Next j
'    Next i
    
    Set oAl = olk.Session.AddressLists("Contacts")

    oAl.AddressEntries.Add "EX", "test Name", "abc@123.com"    ' add entry
    
    Set oAl = Nothing
    olk.Quit
    Set olk = Nothing
    
End Sub

 

[jsotola]

did more research

this appears to be the simplest way to add a contact

Sub testEmail()


    Dim olk As Outlook.Application
    Dim oCi As Outlook.ContactItem
    
    Set olk = New Outlook.Application
    Set oCi = olk.CreateItem(olContactItem)
    
    oCi.FullName = Cells(Application.ActiveCell.Row, 5).Value
    oCi.HomeTelephoneNumber = Cells(Application.ActiveCell.Row, 6).Value
    oCi.Email1Address = Cells(Application.ActiveCell.Row, 7).Value

    oCi.Save
    
    Set oCi = Nothing
    Set olk = Nothing
    
End Sub

 

[jsotola]

bit more research ........ and here it is

it sends a test email

Sub testEmail()


    Dim oAp As Outlook.Application
    Dim oCi As Outlook.ContactItem
    Dim oMi As Outlook.MailItem
    
    Set oAp = New Outlook.Application
    
'    Set oCi = olk.CreateItem(olContactItem)
    
'    oCi.FullName = Cells(Application.ActiveCell.Row, 5).Value
'    oCi.HomeTelephoneNumber = Cells(Application.ActiveCell.Row, 6).Value
'    oCi.Email1Address = Cells(Application.ActiveCell.Row, 7).Value
'    oCi.Save
    
'    Set oCi = Nothing
    
    Set oMi = oAp.CreateItem(olMailItem)
    
    oMi.Body = "test message"
    oMi.Subject = "subject line"
'    oMi.To = "abc123@someMailServer.com"
    oMi.To = Cells(Application.ActiveCell.Row, 7).Value
    
    oMi.Send
    
    Set oMi = Nothing
    Set oAp = Nothing
    
End Sub

 

[s.ridd]

here is code that adds entry into the address book without opening outlook

    Set olk = New Outlook.Application

This line of code opens Outlook, it just doesn't display it. As there can only ever be one version of Outlook open the use of the New keyword isn't necessary. You can close Outlook again by using Application.Quit or in this case oAp.Quit but this isn't recommended when you are displaying items.

Your second piece of code is similar to the original code posted, except it uses late binding, and doesn't have some of the superfluous variables and code. The use of late binding means that the code won't automatically work when switched between Office 2013 and Office 2007.

Lastly the original brief (despite the original code posted doing otherwise) was to display both the contact and message rather than save and send. I would therefore amend your code to look something like below

Sub testEmail()

    Dim oAp As Object 'Outlook.Application
    Dim oCi As Object 'Outlook.ContactItem
    Dim oMi As Object 'Outlook.MailItem
    
    Set oAp = CreateObject("Outlook.Application") 'Outlook.Application
    
'    Set oCi = oAp.CreateItem(2)
    
'    With oCi
'        .FullName = Cells(Application.ActiveCell.Row, 5).Value
'        .HomeTelephoneNumber = Cells(Application.ActiveCell.Row, 6).Value
'        .Email1Address = Cells(Application.ActiveCell.Row, 7).Value
'        .Display
'    End With
    
    Set oMi = oAp.CreateItem(0)
    
    With oMi
        .Body = "test message"
        .Subject = "subject line"
'       .To = "abc123@someMailServer.com"
        .To = Cells(Application.ActiveCell.Row, 7).Value
        .Display
    End With
    
'    oAp.Quit
    Set oCi = Nothing
    Set oMi = Nothing
    Set oAp = Nothing
    
End Sub

You might also put the information from Cells(Application.ActiveCell.Row, 7).Value into a variable if you wished too

Hope this helps

Simon

 

[jsotola]

s.ridd thanks for a quick lesson

i am not familiar with the term "late binding" and why it would not work between office versions

something new for me to learn

 

[jsotola]

ok, i looked it up

i use it already (just did not know what it was called)

i normally use early binding during development because of Intellisense being available for command completion ( methods, properties ... etc)

then i change the definitions to late binding (same as you did) because the end user does not have to worry about proper references being loaded