Linking to outlook across an exchange server

[BenRatcliffe]

Hi There,

New to the forum but read many an article which have helped me no end in developing vba applications.

I'm a tad stuck though so i thought i'd register and ask the question. I have created an application that links to a oulook mailbox which is held on theexchange server.

I had previously set it up with my own inbox (on the exchange server as well) and basically what this part of the application does is give a form view of the inbox which is linked and then on a button click event it adds the current email to a table with a primary and secondary key.

My question is not how to get it to work as i have successfully managed that with my own inbox, but now i have had a new inbox created which has a shared ownership over several people, everything seems to be working fine except the emails will no longer add to the table. It doesn't throw an exception but when i check the table it hasn't added the record.

I am really scratching my head as when I re-link to my own inbox everything starts working again fine. I aparently have ownership of the shared inbox so i don't think permissions is a problem.

I have also noticed that i can run an append query using information from the shared inbox fine, its just it doesn't like it using the form!

I have posted my code below for this specific procedure:

Many Thanks
Ben

Option Compare Database
Dim TEMP As String
Dim temp2 As String
 
 
Private Sub Command8_Click()
 
' convert recieved date & temp_tracker_id in string values
 
TEMP = Received
temp2 = temp_tracker_id.Value
 
' insert record into TBL_INBOX where recieved date = TEMP and tracker_ID = temp2
 
DoCmd.RunSQL ("INSERT INTO TBL_INBOX ( Received, [From], Subject, Contents, Tracker_ID ) SELECT Inbox.Received, Inbox.From, Inbox.Subject, Inbox.Contents, [P&T Tracker].Tracker_ID From Inbox, [P&T Tracker]WHERE (((Inbox.Received)=#" + TEMP + "#) AND ((Inbox.From)='" + From + "') AND (([P&T Tracker].Tracker_ID)=" + temp2 + "));")
 
MsgBox ("Email Added")
 
End Sub

[NeoPa]

Ben,

Firstly welcome to TSDN :)

As far as your problem goes I will share my thoughts but they may not be too helpful as my experience in this area (Exchange/Outlook) is quite limited.
It seems to me though, from the fact that it works for your mailbox but not for the other, that there may be one of two issues showing here :

1. Something in your code specifically refers to your mailbox. Unlikely. I checked your code and, although not every possible item is visible (some come from data), there's nothing I could see that might support this.
2. Some rights issues with the Exchange mailbox. I assume you've tried accessing the other mailbox from the same machine using Outlook (or similar client).
   Even assuming you have, this does not preclude this being the issue as the permissioning on Exchange can be complicated and flexible.

These are just my thoughts. I can't see any clear reason why this might be behaving this way.

[BenRatcliffe]

Thanks for the response, and the welcome :D NeoPa,

I had thought it might be that somewhere in my code that it links specifically to a mailbox, but my connection to outlook simply refers to table_inbox where i just changed the connection from my inbox to the shared (tracker) inbox, it rely's on exactly the same fields (from, subject, received date & contents). It really has me perplexed as I can view the emails fine, I can manually query it by entering parameters myself, but unfortunately I need to link emails automatically as it is a call logging system. I can view the inbox in outlook and send and receive emails fine.

The only other option i can think of is to run an import query on the inbox every time it opens to find emails it doesn't have yet, but that is cumbersome as not all emails will neccessarily be needed to linked to a call.

I guess next step is to ask the IT guys what permissions were set up on the account, but as I have been placed owner i would assume I had all rights anyhow.

Many Thanks
Ben

Ben,

Firstly welcome to TSDN :)

As far as your problem goes I will share my thoughts but they may not be too helpful as my experience in this area (Exchange/Outlook) is quite limited.
It seems to me though, from the fact that it works for your mailbox but not for the other, that there may be one of two issues showing here :

1. Something in your code specifically refers to your mailbox. Unlikely. I checked your code and, although not every possible item is visible (some come from data), there's nothing I could see that might support this.
2. Some rights issues with the Exchange mailbox. I assume you've tried accessing the other mailbox from the same machine using Outlook (or similar client).
   Even assuming you have, this does not preclude this being the issue as the permissioning on Exchange can be complicated and flexible.

These are just my thoughts. I can't see any clear reason why this might be behaving this way.

[ADezii]

Hi There,

New to the forum but read many an article which have helped me no end in developing vba applications.

I'm a tad stuck though so i thought i'd register and ask the question. I have created an application that links to a oulook mailbox which is held on theexchange server.

I had previously set it up with my own inbox (on the exchange server as well) and basically what this part of the application does is give a form view of the inbox which is linked and then on a button click event it adds the current email to a table with a primary and secondary key.

My question is not how to get it to work as i have successfully managed that with my own inbox, but now i have had a new inbox created which has a shared ownership over several people, everything seems to be working fine except the emails will no longer add to the table. It doesn't throw an exception but when i check the table it hasn't added the record.

I am really scratching my head as when I re-link to my own inbox everything starts working again fine. I aparently have ownership of the shared inbox so i don't think permissions is a problem.

I have also noticed that i can run an append query using information from the shared inbox fine, its just it doesn't like it using the form!

I have posted my code below for this specific procedure:

Many Thanks
Ben

Option Compare Database
Dim TEMP As String
Dim temp2 As String
 
 
Private Sub Command8_Click()
 
' convert recieved date & temp_tracker_id in string values
 
TEMP = Received
temp2 = temp_tracker_id.Value
 
' insert record into TBL_INBOX where recieved date = TEMP and tracker_ID = temp2
 
DoCmd.RunSQL ("INSERT INTO TBL_INBOX ( Received, [From], Subject, Contents, Tracker_ID ) SELECT Inbox.Received, Inbox.From, Inbox.Subject, Inbox.Contents, [P&T Tracker].Tracker_ID From Inbox, [P&T Tracker]WHERE (((Inbox.Received)=#" + TEMP + "#) AND ((Inbox.From)='" + From + "') AND (([P&T Tracker].Tracker_ID)=" + temp2 + "));")
 
MsgBox ("Email Added")
 
End Sub

I really do not have enough information to go on, and I'm probably off base here, but in the 3rd Section of your WHERE Clause:

(...AND [P&T Tracker].Tracker_ID)=" + temp2 + ";")
 

Shouldn't [P&T Tracker].Tracker_ID be looking for a Numeric Value? It seems to me that you are making the comparison to a String Value via the Declaration and Assignment Dim temp2 As String / temp2 = temp_tracker_id.Value, which would implicitly make temp2 a String, wouldn't it? It would appear to me that it would be either:

(...AND [P&T Tracker].Tracker_ID)='" & temp2 & "';")     'temp2 As String
 
(...AND [P&T Tracker].Tracker_ID)=" & temp2 & ";")      'temp2 As Numeric
 

Like I said, a shot in the dark!

[NeoPa]

I think you may be confused by the "+" characters here ADezii.
They are perfectly valid string concatenators in VBA. They differ from the "&" concatenator in that "X" & Null = "X" whereas "X" + Null = Null. In other words, they perpetuate the Null rather than the string.
Also, temp2 is perfectly valid as a string. A SQL string is just that, a string. If the contents match what is expected by the time the string is parsed by the SQL engine, then that's all that matters. That is to say, if the resultant string after the VBA statement creating it is correct, it doesn't matter how the VBA got there.
That may explain the history of using a string variable in fact. A Null result causing the SQL effectively to disappear. If that's so then using Nz() would prove a better alternative.

[NeoPa]

As an example, a clever place to use "+" in strings would be when a control on a form (Me.Surname) can possibly be Null but if it's not, then the query should select on it :

strSQL = "SELECT * " & _
         "FROM [MyTable] " & _
         "WHERE MyTable.Surname = '" + Me.Surname + "'"

If Me.Surname is Null, then everything in the third line simply disappears.

[ADezii]

As an example, a clever place to use "+" in strings would be when a control on a form (Me.Surname) can possibly be Null but if it's not, then the query should select on it :

strSQL = "SELECT * " & _
         "FROM [MyTable] " & _
         "WHERE MyTable.Surname = '" + Me.Surname + "'"

If Me.Surname is Null, then everything in the third line simply disappears.

Thanks NeoPa, for taking the time and pointing this out to me. It looks like you should post the next Tip with this as the Topic (LOL). If by any chance you should, I would like to leave the BLOB Tip up and running for 2 weeks since I think the response should be good, and I put a lot of time into it. Thanks again!

[NeoPa]

I hadn't really thought of it ADezii, but if you think it might be well received then I might if I get the time. Otherwise, don't feel obliged to replace the BLOB tip until you feel it's run its course. The "Tip of the Week" can easily be treated as a "Tip of the Timeframe that Suits our Purpose". No worries :)
Also remember, the HowTos are there for everyone, so if you find a question that any one of them is relevant to, post a link to it in your reply. Our HowTo section is particularly valuable with all the work you've put into it. Let's point the traffic that way where we can.

[ADezii]

I hadn't really thought of it ADezii, but if you think it might be well received then I might if I get the time. Otherwise, don't feel obliged to replace the BLOB tip until you feel it's run its course. The "Tip of the Week" can easily be treated as a "Tip of the Timeframe that Suits our Purpose". No worries :)
Also remember, the HowTos are there for everyone, so if you find a question that any one of them is relevant to, post a link to it in your reply. Our HowTo section is particularly valuable with all the work you've put into it. Let's point the traffic that way where we can.

Your advice is always appreciated, and definately will be kept in mind. You and I can even create a sequel (not SQL) called NeoPa vs The BLOB!. (LOL).

[BenRatcliffe]

Your advice is always appreciated, and definately will be kept in mind. You and I can even create a sequel (not SQL) called NeoPa vs The BLOB!. (LOL).

LOL just caught up on this thread, unfortunately i was training all day today so i haven't had a chance to go back to look at the code, I placed the + as its what i have used in other programming languages and it worked so i have always left it lol.

I converted the ID to a string value as well as the date as I was getting a data mismatch exception so that was the only alternative I could see, I only needed to match the columns to find the correct record in the inbox. I'll post the rest of the code on the form tomorrow so it makes a bit more sense :)

Have a good evening!
Ben

[ADezii]

LOL just caught up on this thread, unfortunately i was training all day today so i haven't had a chance to go back to look at the code, I placed the + as its what i have used in other programming languages and it worked so i have always left it lol.

I converted the ID to a string value as well as the date as I was getting a data mismatch exception so that was the only alternative I could see, I only needed to match the columns to find the correct record in the inbox. I'll post the rest of the code on the form tomorrow so it makes a bit more sense :)

Have a good evening!
Ben

After doing this for so long, "+" and "&" look exactly alike! (LOL).

[BenRatcliffe]

Good Morning!

Well i had a thought on the use of + and & and tried using the & instead of + (thats alot of and's :S) but kept the fields as the orginal date and integer format, it didn't throw a data mismatch exception but still didn't work!

I would post the rest of the code on the form but its not really relevant to the code in question.

Any other idea's would be appreciated, or perhaps any ideas on another method to extract emails from the inbox and place into a table linking a call (tracker_id). I am a bit lost now lol

Cheers
Ben

[BenRatcliffe]

Just to let you know I figured a way around it.

I used the contents of the columns in an insert into statement rather than querying the actual inbox, problem solved!

Option Compare Database
Dim temp_id As String
 
 
Private Sub Command8_Click()
 
temp_id = temp_tracker_id
 
' insert record into TBL_INBOX CORRESPONDING COLUMNS ON FORM
 
DoCmd.RunSQL "INSERT INTO [TBL_INBOX] ([Received],[FROM],[CONTENTS],[SUBJECT],[TRACKER_ID]) VALUES (#" & Me.Received & "#,'" + Me.From + "','" + Me.Subject + "','" + Me.Contents + "','" + temp_id + "')"
 
MsgBox ("Email Added")
 
 
End Sub

[BenRatcliffe]

Just on a note, I wanted to post this as it took me a while to work it out and it might be handy for other people, this is a bit of code that emails all people an automated email that appear in a table from a select query (basically all people that have an outstanding call to attend to). unfortunately outlook comes up with the stupid security warning, if anyone has a way round that i would appreciate it muchly!

Thanks for the help on this people :)

Ben

Edit: i just thought i'd say part of it isn't hard coded my me, i borrowed it from another bit of code i saw and changed it a bit lol

Private Sub btn_reminders_Click()
 
 
On Error GoTo reminder_error
 
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String
 
   'Set environment
   Set db = CurrentDb
   DoCmd.SetWarnings False
 
   'Open the controlling recordset
   Set rs = db.OpenRecordset("QRY_DUE_EMAILS")
   While Not rs.EOF
 
       'For each record (in qry) send an email
 
    DoCmd.SendObject acSendNoObject _
    , _
    , _
    , _
    rs![email], _
    , _
    , _
    "Tracker Database Reminder", _
    "Dear " & rs![email] & vbCrLf & vbCrLf & "This is an automated email to remind you that a call has been logged with ID: " & rs![Tracker_ID] & " and Subject: " & rs![Correspondence Subject] & ". This has the response deadline date of " & rs![Reply Deadline] & vbCrLf & vbCrLf & "Regards - Tracker Database", _
    False
 
 
       'Cycle on to the next record
       rs.MoveNext
   Wend
   rs.Close
   Set rs = Nothing
   'Control recordset now closed
 
   'Reset environment
   DoCmd.SetWarnings True
 
 
 
Exit_btn_reminders_Click:
    Exit Sub
 
reminder_error:
 
MsgBox ("Action was cancelled")
 
 
End Sub