Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate email address
I have a macro that uses "SendMail" to send a workbook to a list of users.
If there is an invalid email address, it will not send the email at all. Is there a way that I can verify the email addresses before the workbook is sent? Thanks in advance, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate email address
That can be a little tricky... Outlook gurads that list to ensure that
virus's don't use it. Depending on what version you are on this might work for you... Option Explicit Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E Const g_strMAPILogOn As String = "MS Exchange Settings" Const g_strAddressList As String = "Global Address List" Const g_strEMailAddressIdentifier As String = "SMTP" Sub Test() MsgBox GetEMailAddress("James Thomlinson") End Sub Public Function GetEMailAddress(ByVal strName As String) As String Dim objSession As MAPI.Session Dim objField As MAPI.Field Dim MyAddressList As MAPI.AddressList Dim MyAddressEntries As MAPI.AddressEntries Dim MyEntry As MAPI.AddressEntry Dim SomeEntry As MAPI.AddressEntry Dim MyRecipient As MAPI.Recipient Dim v As Variant Dim strReturnValue As String 'Initialize Local Variables strReturnValue = "No Address Found" 'Retrun Value if not found ' Create Session object and Logon. Set objSession = CreateObject("MAPI.Session") objSession.Logon (g_strMAPILogOn) 'Create the Address list from the Global Address List Set MyAddressList = objSession.AddressLists(g_strAddressList) If MyAddressList Is Nothing Then MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical Error" Exit Function End If 'Initialize MyAddressEntires with the entries in the Address List Set MyAddressEntries = MyAddressList.AddressEntries 'Traverse through the entries searching for a match For Each SomeEntry In MyAddressEntries Set MyEntry = SomeEntry If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES) ' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property (PT_MV_TSTRING). ' Therefore, you need to extract the individual members. For Each v In objField.Value If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then strReturnValue = Mid(v, 6, 256) Exit For End If Next 'Next Field Value Exit For End If Next 'Next Address Entry 'Return Function Value GetEMailAddress = strReturnValue 'Housekeeping Set objField = Nothing Set MyAddressList = Nothing Set MyAddressEntries = Nothing Set MyEntry = Nothing Set MyRecipient = Nothing objSession.Logoff Set objSession = Nothing End Function You need to reference the Microsoft CDO 1.2? (Part of the install for outlook) library to use this code. It takes a person's name and returns their E-mail address from the global address list... -- HTH... Jim Thomlinson "Ken Valenti" wrote: I have a macro that uses "SendMail" to send a workbook to a list of users. If there is an invalid email address, it will not send the email at all. Is there a way that I can verify the email addresses before the workbook is sent? Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to validate email address during data entry in Excel | Excel Discussion (Misc queries) | |||
Validate Email address entry | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Validate an email address | Excel Programming | |||
relative address in validate | Excel Programming |