outlook to access via a Right Click menu?

[iankerry]

Hi

I dont know if the following is possible, or if anyone might point me in the right direction for more help?

We organise over 700 events a year - each event is a record - and most of our emails received are to do with one of the those records. We spend a lot of time receiving an email, going to database, and finding that person records, so that we can make the often small chage to their record. I have made this as painless as possible but because we do it so often it can take up a lot of time just finding the records.

So, might it be possible to be able to right click on an email address in outlook, and have a macro (?) run from one of the items on the drop down list, that would go to the database and bring up all the records for the person with that email address?

Would be brill to hear from someone!

Thanks

ian

 

[darbid]

What version of Office (OUTLOOK) are you on?
Does the email have something in it or can you start a process of adding something to it so that you know which Record in the database it belongs to?

i was getting ready to say it was not possible for Outlook 2003 but it might be.

http://www.outlookcode.com/codedetail.aspx?id=314

The problem with the right click is the user must have already clicked the item that they want to move. There will be problems if they have item 1 clicked or selected, then they right click down a little on another item, meaning to send this to Access. A right click does not select the new item.

 

[iankerry]

Hi

Thanks for reply.

We are on a mix of 2007 and 2010. Though we could all be on 2010.

I did wonder about trying to put an EventID number in it, but felt it would work - ans some people just start new emails to us.

The email address they are using though will be stored on our database, it is the only bit of info that we can be sure of.

I'll give that link a good look as see how far i can get!

thanks
ian

 

[iankerry]

HI

I have never work in vba in outlook and the instructions on the link are not great.

Can you tell me how to get to the correct place to try the code?

I have tried tools: macros: visual basic editor

it may be that 2007 looks different - the instructions says:
'To use this example, copy and paste the following code into the ThisOutlookSession 'object of a new VBA project in Outlook 2002 or Outlook 2003.

but i dont know what this means! Can you advise?

thanks
ian

 

[iankerry]

Hi

Got it working! once I had turned on the views so that the menu appeared on the left!

Got the code working too, so a test button appears on the contacts - need to have a good long think about code that might do what i want now!

thanks for you helps

ian

 

[darbid]

You need to work out how to link all the things in outlook to your access table. ie how does the code decide to which record it belongs - and once you have done this what does the code do when it cannot do this.

Then come back as I think a button would be better than a contect menu.

 

[iankerry]

Thanks again for replying.

When i get some time i will look at a button in outlook. Although within access i am fairly confident in programming buttons i have never done so in Outlook, but perhaps it is very similar?

I will perhpas look at doing a button in access that will do the trick and then try and do the same in outlook?

If it was a button in access, it would take the email address of the record/field i am on and then use this to filter a form on the email address, thereby bringing up all the records pertaining to the person with that email address.

sounds simple but i bet it isnt!

cheers

ian

 

[darbid]

As I said your real task is to decide where an email or other outlook item belongs in access.

Some things also to think about.

I used VB6 to make an add-in for outlook (a little dll) this added a button to outlook. People select an email and then click the button. All it does is send the email as an object to access and then I did all my code in access because I am more confident in access and I could update the code more easily.

You could do this as well. The only problem is that you will not be able to use VB6 for 2007 7 2010 and to be honest I am not up with the Ribbon (where all the buttons are). You will have to use VB.NET to do this now and that is a little different to VBA.

If you want to manually distribute it then we can write the code in VBA and just add it to Outlook manually. It is easy to add a button once you have a "sub" in vba.

I can pretty much give you a finished function for outlook as above, you then need to work on it in Access. Does that sound interesting?

 

[iankerry]

Hi Darbid, that sounds fantastic - are you able to do this? Won't it take a lot of time?

<<As I said your real task is to decide where an email or other outlook item belongs in access.>>

i don't quite understand this question, sorry. it is the email address rather than the email body, that i need to send to access. If this can be used in a query or as a filter on a form, then that should sort it.

<<Some things also to think about.

I used VB6 to make an add-in for outlook (a little dll) this added a button to outlook. People select an email and then click the button. All it does is send the email as an object to access and then I did all my code in access because I am more confident in access and I could update the code more easily.>>

Did this send the whole email to access? all i need of course is the email address

<<You could do this as well. The only problem is that you will not be able to use VB6 for 2007 7 2010 and to be honest I am not up with the Ribbon (where all the buttons are). You will have to use VB.NET to do this now and that is a little different to VBA.>>

yes i haven't ever done .net either, any solution should be in VBA so at least one i understand it i can amend it.

<<If you want to manually distribute it then we can write the code in VBA and just add it to Outlook manually. It is easy to add a button once you have a "sub" in vba.>>

yes this seems the simplest way to do it - and i like simple

<<I can pretty much give you a finished function for outlook as above, you then need to work on it in Access. Does that sound interesting?>>

Any help like this is greatly appreciated. I am not a great programmer, but i am keen to know more and play with things till i understand them and get them to work.

Cheers
Ian

 

[darbid]

ok I am not going to spoon feed, but if you ask me a direct question I will try to answer.

open VB editor in outlook and copy this.

Then on the tool bar in outlook right click > customize > categories > macros. Then you should see this in the right list. Just drag it up onto and existing toolbar. You can right click it and change its name and icon if you want. (2007)

Sub emTool()

   '<OUR TOOLBAR BUTTON CLICK EVENT PROCEDURE>

On Error GoTo Err_SendEmailToAccess

Dim appAccess As Object 'late binding of the MSAccess object
Dim objDBase As Object 'late binding of LuTTool MDB
Dim out_Exp As Outlook.Explorer
Dim out_Sel As Outlook.Selection
Dim out_mail As Outlook.MailItem
Dim out_Rec As Outlook.Recipient
Dim fs As Object
Dim out_emailAddress As String



'get the active explorer
Set out_Exp = Application.ActiveExplorer

If out_Exp.CurrentFolder.WebViewOn = False Then
        Set out_Sel = out_Exp.Selection
    Else
        MsgBox "selection wrong type"
        Set out_Exp = Nothing
        Exit Sub
    End If

'first make sure they have only choosen one item if not exit sub
If out_Sel.Count > 1 Then
    MsgBox "Please only select one item"
    Exit Sub
Else
    If out_Sel.Count = 0 Then
    MsgBox "Please select somthing"
    Exit Sub
    End If
End If

'check that it is in fact an email that has been choose
If Not out_Sel.Item(1).Class = olMail Then
    MsgBox "Please only choose email items"
    Exit Sub
End If


'next try to get the email item - if there is an error 13 then it is encrypted
On Error Resume Next

Set out_mail = out_Sel.Item(1)

If Err.Number = 13 Then
    Err.Clear
    MsgBox "Email is encrypted"
    Exit Sub
End If



'here it depends on what you want sender, to, cc bcc


For Each out_Rec In out_mail.Recipients

   out_emailAddress = out_Rec.Address

Next

'next bit does the access part

'Set appAccess = GetObject(, "Access.Application")
'
'If Err.Number = 429 Then
'    Err.Clear
'    MsgBox "Access is closed, please start access"
'    Exit Sub
'End If
'
'Set objDBase = appAccess.CurrentDb
'
'If Left(Dir(objDBase.Name), 7) = "add the full name of your access mdb" Then
'    appAccess.Run "ReceiveEmailFromOutlook", out_emailAddress
'End If
'
'Set appAccess = Nothing
'Set objDBase = Nothing


Exit_SendEmailToAccess:
    Exit Sub

Err_SendEmailToAccess:
    MsgBox "error"
    Resume Exit_SendEmailToAccess

End Sub

You still have to decide which email address you want.

Now make a mdb or in your existing mdb in a module add a public method like this. YOu need to add the full name of your mdb to the above code.

Public Sub ReceiveEmailFromOutlook(outAddress as string)

          msgbox "This address was sent from outlook " & outaddress

end sub

 

[iankerry]

Hi Thanks so much for this - will take a look over the weekend as i have to leave now.

best
wishes

ian

p.s. thanks for the reply on the other thread too!

 

[iankerry]

Hi

couldn't resist looking at this when i got home!

At the moment i am stumbling on this line

Set appAccess = GetObject(, "Access.Application")

after googling it seemd to be a Reference problem but i have tried several references and still stumble.

Any ideas?

cheers

ian

 

[darbid]

You can add a reference to access but you don't have to. You have access On your computer and it is running?

What error message are you getting?

 

[iankerry]

You can add a reference to access but you don't have to. You have access On your computer and it is running?

What error message are you getting?

Yes i am running access 2007. and it is up on screen.

run time error 429

activex component cant create object.

I.

 

[darbid]

There is a "on error resume next" up a little bit so you should not be getting that. Plus the next part catches that error, which happens if access is closed

 

[iankerry]

Hi
I have managed to make some progress, but i have a couple of questions

Firstly, Darbid you asked which part of the email i was after - i am after the email address of the person that sent it - the code at the monent gives my address. I have played around with Sender.Address but to no avail. if you could point me in the right direction for this.

Secondly, can you tell me what this line does:
If Left(Dir(objDBase.Name), 7) = "z:\databases\flicks database.accdb" Then

because the next line is the appAccess.Run line and it doesnt run so the above line must be false!

many thanks!

ian

 

[darbid]

I showed you the recipients Of the email the senders address is just

.SenderEmailAddress

The other line of code you talk about is the name of the database, I think it is only the file name. Take off the left function that is my fault.

 

[iankerry]

Hi Darbid

Thanks for speedy reply.

I fear i have bitten off more than i can chew! I have spent hours on it so far and only make a little progress.

I dont even understand some of your answer or know what to do.

you said take the LEFT FUNCTION formt he line - well i have tried various ways of taking out the left function but it doesnt like the line i am left with - e.g. do you still need the IF, am i just to write

Dir(objDBase.Name), 7 = "z:\databases\flicks database.accdb"

but that gives an error.

On the other matter i assume i have to do a Dim Statement so i try

Dim in_add As Outlook.SenderEmailAddress - i get "user defined type not defined.

and even when i have created the Dim, i then need to Set it and i am completely lost.

but thanks for trying.

Cheers

ian

 

[darbid]

Sub emTool()

   '<OUR TOOLBAR BUTTON CLICK EVENT PROCEDURE>

On Error GoTo Err_SendEmailToAccess

Dim appAccess As Object 'late binding of the MSAccess object
Dim objDBase As Object 'late binding of LuTTool MDB
Dim out_Exp As Outlook.Explorer
Dim out_Sel As Outlook.Selection
Dim out_mail As Outlook.MailItem
Dim out_Rec As Outlook.Recipient
Dim fs As Object
Dim out_emailAddress As String



'get the active explorer
Set out_Exp = Application.ActiveExplorer

If out_Exp.CurrentFolder.WebViewOn = False Then
        Set out_Sel = out_Exp.Selection
    Else
        MsgBox "selection wrong type"
        Set out_Exp = Nothing
        Exit Sub
    End If

'first make sure they have only choosen one item if not exit sub
If out_Sel.Count > 1 Then
    MsgBox "Please only select one item"
    Exit Sub
Else
    If out_Sel.Count = 0 Then
    MsgBox "Please select somthing"
    Exit Sub
    End If
End If

'check that it is in fact an email that has been choose
If Not out_Sel.Item(1).Class = olMail Then
    MsgBox "Please only choose email items"
    Exit Sub
End If


'next try to get the email item - if there is an error 13 then it is encrypted
On Error Resume Next

Set out_mail = out_Sel.Item(1)

If Err.Number = 13 Then
    Err.Clear
    MsgBox "Email is encrypted"
    Exit Sub
End If



'here it depends on what you want sender, to, cc bcc

out_mail.SenderEmailAddress


For Each out_Rec In out_mail.Recipients

   out_emailAddress = out_Rec.Address

Next

'next bit does the access part

'Set appAccess = GetObject(, "Access.Application")
'
'If Err.Number = 429 Then
'    Err.Clear
'    MsgBox "Access is closed, please start access"
'    Exit Sub
'End If
'
'Set objDBase = appAccess.CurrentDb
'
msgbox "this is what you need to put next" &  Dir(objDBase.Name)

'If Dir(objDBase.Name) = "add the full name of your access mdb" Then
'    appAccess.Run "ReceiveEmailFromOutlook", out_emailAddress
'End If
'
'Set appAccess = Nothing
'Set objDBase = Nothing


Exit_SendEmailToAccess:
    Exit Sub

Err_SendEmailToAccess:
    MsgBox "error"
    Resume Exit_SendEmailToAccess

End Sub

 

[iankerry]

Thanks for persevering.

i can get as far as

MsgBox "this is what you need to put next" & Dir(objDBase.Name)

if i comment this statement out i get onto the

If Dir(objDBase.Name) = "z:\databases\flicks database.accdb" Then

now, same problem that i asked about before - this is an if statement and the following line is the appAccess.Run line and it doesnt run.

if i comment out this line (perhaps not unsurprisingly) I get

r/t error 2517 cannot find the "receiveEmailfromOutlook procedure.

i have put the procedure you sent into a module in my database - is that the correct place.

can we give it one more try? i don't want to wear out your good will.

ian