By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Linking to outlook across an exchange server

BenRatcliffe
P: 9
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim TEMP As String
  3. Dim temp2 As String
  4.  
  5.  
  6. Private Sub Command8_Click()
  7.  
  8. ' convert recieved date & temp_tracker_id in string values
  9.  
  10. TEMP = Received
  11. temp2 = temp_tracker_id.Value
  12.  
  13. ' insert record into TBL_INBOX where recieved date = TEMP and tracker_ID = temp2
  14.  
  15. 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 + "));")
  16.  
  17. MsgBox ("Email Added")
  18.  
  19. End Sub
Jan 7 '08 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
Jan 7 '08 #2

BenRatcliffe
P: 9
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.
Jan 7 '08 #3

ADezii
Expert 5K+
P: 8,623
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
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim TEMP As String
  3. Dim temp2 As String
  4.  
  5.  
  6. Private Sub Command8_Click()
  7.  
  8. ' convert recieved date & temp_tracker_id in string values
  9.  
  10. TEMP = Received
  11. temp2 = temp_tracker_id.Value
  12.  
  13. ' insert record into TBL_INBOX where recieved date = TEMP and tracker_ID = temp2
  14.  
  15. 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 + "));")
  16.  
  17. MsgBox ("Email Added")
  18.  
  19. 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:
Expand|Select|Wrap|Line Numbers
  1. (...AND [P&T Tracker].Tracker_ID)=" + temp2 + ";")
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. (...AND [P&T Tracker].Tracker_ID)='" & temp2 & "';")     'temp2 As String
  2.  
  3. (...AND [P&T Tracker].Tracker_ID)=" & temp2 & ";")      'temp2 As Numeric
  4.  
Like I said, a shot in the dark!
Jan 7 '08 #4

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jan 7 '08 #5

NeoPa
Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * " & _
  2.          "FROM [MyTable] " & _
  3.          "WHERE MyTable.Surname = '" + Me.Surname + "'"
If Me.Surname is Null, then everything in the third line simply disappears.
Jan 7 '08 #6

ADezii
Expert 5K+
P: 8,623
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * " & _
  2.          "FROM [MyTable] " & _
  3.          "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!
Jan 8 '08 #7

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jan 8 '08 #8

ADezii
Expert 5K+
P: 8,623
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).
Jan 8 '08 #9

BenRatcliffe
P: 9
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
Jan 8 '08 #10

ADezii
Expert 5K+
P: 8,623
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).
Jan 8 '08 #11

BenRatcliffe
P: 9
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
Jan 9 '08 #12

BenRatcliffe
P: 9
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!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim temp_id As String
  3.  
  4.  
  5. Private Sub Command8_Click()
  6.  
  7. temp_id = temp_tracker_id
  8.  
  9. ' insert record into TBL_INBOX CORRESPONDING COLUMNS ON FORM
  10.  
  11. DoCmd.RunSQL "INSERT INTO [TBL_INBOX] ([Received],[FROM],[CONTENTS],[SUBJECT],[TRACKER_ID]) VALUES (#" & Me.Received & "#,'" + Me.From + "','" + Me.Subject + "','" + Me.Contents + "','" + temp_id + "')"
  12.  
  13. MsgBox ("Email Added")
  14.  
  15.  
  16. End Sub
Jan 14 '08 #13

BenRatcliffe
P: 9
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_reminders_Click()
  2.  
  3.  
  4. On Error GoTo reminder_error
  5.  
  6. Dim db As DAO.Database, rs As DAO.Recordset
  7. Dim sSQL As String
  8.  
  9.    'Set environment
  10.    Set db = CurrentDb
  11.    DoCmd.SetWarnings False
  12.  
  13.    'Open the controlling recordset
  14.    Set rs = db.OpenRecordset("QRY_DUE_EMAILS")
  15.    While Not rs.EOF
  16.  
  17.        'For each record (in qry) send an email
  18.  
  19.     DoCmd.SendObject acSendNoObject _
  20.     , _
  21.     , _
  22.     , _
  23.     rs![email], _
  24.     , _
  25.     , _
  26.     "Tracker Database Reminder", _
  27.     "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", _
  28.     False
  29.  
  30.  
  31.        'Cycle on to the next record
  32.        rs.MoveNext
  33.    Wend
  34.    rs.Close
  35.    Set rs = Nothing
  36.    'Control recordset now closed
  37.  
  38.    'Reset environment
  39.    DoCmd.SetWarnings True
  40.  
  41.  
  42.  
  43. Exit_btn_reminders_Click:
  44.     Exit Sub
  45.  
  46. reminder_error:
  47.  
  48. MsgBox ("Action was cancelled")
  49.  
  50.  
  51. End Sub
Jan 14 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.