Check From permisions

[GBetter]

Hello All,

I am using Excel to send mail from Outlook. (But I think this is an outlook query).

Is there anyway to check the From permissions,

My problem is that if I send 20 emails using Excel VBA if the From field (SentOnBehalfOfName) used is incorrect (or the user does not have permission) the email fails.

If I could figure it out in Outlook Im sure I can then make Excel do it for me.

I already check the To names using the below,


mleItem.Recipients.ResolveAll
For i = 1 To mleItem.Recipients.Count
If Not mleItem.Recipients.Item(i).Resolved Then
NamePass = False
End If
Next i

Is there a way to do this this with the from field?

[Oorang]

Hi GB,
Welcome to the board :) The issue of email address validation is a bit sticky because the only way to truly know an address is correct is to send something to it and get a positive response (a lack of a failure message does not neccasarily mean an email was recieved). There are two commonly accepted approaches which you can use. One is to simply maintain a list of valid emails. If the entry is not on the list you notfy the user to correct it prior to sending. The second is to use pattern recognition to see if the value follows a pattern consistent with an email address. There are several approaches to this last, I have posted a simple one below. There is more discussion here (http://www.mrexcel.com/board2/viewtopic.php?t=243035&highlight=email), and some people have solved the problem using regular expressions instead.


Function IsValidEmail(email As String) As Boolean
Const strEmailPattern_c As String = "?*@?*.?*"
Const lngMinLenB As Long = 8 '*More* than 4 Characters, 1@2.3 being the shortest possible valid address.
Const lngMaxLenB As Long = 102 'Must be 50 chars or less
Dim lngEmailLenB As Long
lngEmailLenB = VBA.LenB(email)
If lngEmailLenB > lngMinLenB Then
If lngEmailLenB < lngMaxLenB Then
If email Like strEmailPattern_c Then
IsValidEmail = True
End If
End If
End If
End Function

[GBetter]

Hi Oorang,

Thanks for the reply.

Your way of checking email address' is very cool (Ive added it to my code that checks the 'To' address).

However I was hoping for something that could check the From address, I realise your code could check that the from address is an email address however it does not check if that person has access to send from it.

In outlook you can see who you have permission to send from and its this list that I would like to check against.

e.g.
I can send emails from myselft GB@MadeUp.com and also from a group address Group@MadeUp.com so I have permission to send from these two accounts. When I come to run my procedure, I want it to see what I have in the 'From' textbox and check that it is one of these two otherwise dont send (This will have to work for users throghout my group, some of whom have permissions to send from 10/20 email address).

The reason this is a problem, is if you use code to send multiple emails you can trap certain error messages (the To email address is wrong, there are multiple people in your address book that fit the name typed etc) but you can not (well I cant seem to) trap it if your from name is wrong, because it sends and then later errors.

any ideas?

[Oorang]

lol Right:) My mistake, I thought you just typo'd rofl ;)

Ok, well that is not all good news. You can take a little more control in other languages (even in .net) but in VBA I don't think the implementation of the MAPI object exposes any method to test this without actually trying to send something. I suspect the best you are going to be able to do it catch it on first failure with a standard error handler (see below for example). If it was me, I would maintain a pass fail list on a shared drive or someplace else and slowly start building my own permission list. Everytime it fails, record it to your file and every time it works record it. Then you set up your routine to do a lookup in the file first and if the information is there, fine. If not, then just try and record the results for next time.



Sub newEmail()
Const lngErrNoFromPermission_c As Long = -1386807291
Dim mi As Outlook.MailItem
On Error GoTo Err_Hnd
Set mi = Outlook.CreateItem(olMailItem)
mi.Body = "My test email's body."
mi.Subject = "Test"
mi.To = "baz@bar.com"
mi.SentOnBehalfOfName = "foo@barcom"
mi.Send
Exit_Proc:
On Error Resume Next
Exit Sub
Err_Hnd:
If Err.Number = lngErrNoFromPermission_c Then
' Do something special
' Resume someplace special
Else
VBA.MsgBox "Error " & VBA.Err.Number & " in procedure newEmail of VBA Document ThisOutlookSession" & _
vbNewLine & VBA.Err.Description, vbMsgBoxSetForeground Or vbSystemModal, "Error - Project1.ThisOutlookSession.newEmail"
Resume Exit_Proc
End If
End Sub

[GBetter]

Hi Oorang,

Thanks again for giving this some of your time, I do really appreciate it :thumb

I was intrigued by your lines

Const lngErrNoFromPermission_c As Long = -1386807291
If Err.Number = lngErrNoFromPermission_c

Which gave me hope that I could catch the error (sending a test mail before the bulk emails would not have been a problem for me) however I do not get an error.

My outlook still seems to send the message, I then get a delivery report (later into my inbox) stating 'You do not have permission to send to this recipient' Which comes not because of the To address but because of the From address. Its this lack of an initial error that makes me think that it is untrappable.

Its because of this that I believe my only way will be to pull the from permissions from outlook (there stored in there because you can view them), Ive always been a believer that if you can see it on your screen you can retrieve it somehow (it must be stored somewhere in outlooks fields/folders).

Any ideas on the above or if you think you could point me closer to the right direction would be excellent, Im also interested in your error message number -1386807291 do you receieve this error if you send from someone whom you do not have permission?

Cheers
GB

[Oorang]

Yes, on OL2003 if I try to send on behalf on someone I am not authorized to send as, then I get the error. (Altough that is somewhat dependant on the configuration of the exhange server.) Your error doesn't sound the same to me at all. That sounds more like an issue where they (the recipient) are actually blocking your address or IP block. You can't really trap for that error because it's not an error per-se, your email is being properly sent and recieved it's just be sent back, as it were.
You could maintain a list of do not send to addresses and then check the list before you try to send, would that meet your needs?