PDA

View Full Version : Solved: using outlook dialog boxes in excel



philfer
01-13-2009, 11:06 AM
Hello,

I know you can use the Open, SaveAs dialog boxes in Excel VBA by using FileDialog.

Does anyone know if you are able to open the Outlook Global Address book in Excel VBA so the user can choose a recipient(s) which you can then use in the code to send an email

Thanks in advance
Phil

lucas
01-13-2009, 11:55 AM
Phil, this is from a word script but you should be able to change it to suit:
Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "<PR_DISPLAY_NAME>" & vbCr & _
"<PR_POSTAL_ADDRESS>" & vbCr & _
"<PR_OFFICE_TELEPHONE_NUMBER>" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns in a row
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR <> 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point

Selection.Range.Text = strAddress

End Sub

The last line:

Selection.Range.Text = strAddress



is the only part that has anything to do with Word so if you address that you should be able to move forward.

You will need to set a reference to the outlook object library in the vbe.

georgiboy
01-13-2009, 01:42 PM
Updated below.

georgiboy
01-13-2009, 01:53 PM
Select email address and hit send.

philfer
01-15-2009, 10:21 AM
Thanks for that Lucas,

When I get to this point :-

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)

it says :-

Runtime error "438"
Object doesnt support this property or method.

I have set a ref to MS Outlook 12.0 Object Library

Would you know why this error is happening

Thanks
Phil

lucas
01-15-2009, 11:26 AM
Phil, try it in the attached word document.....it works for me and I don't know what the problem might be yet.

I'm running it in office 2003. If you are running it in 2000 or 2007 it might matter.