cobianna
asked on
Move email from within Access
I have found this code that suppossedly will move an email from one folder to another. I can't get it to work. I have a query that has a drop down box that list all the possible folders under a mailbox in my Outlook. I want to be able to select the folder from this drop down and then push a botton to move emails out of the Inbox and into the select folder. I don't know if this code can accomplish this but I would love someones opinion on it. The code is as follows:
Function MoveMailItemTo(strMapNameL
' This procedure moves the item to the specified Outlook mailbox folder. *
' That is identified by: *
' strMapNameLike = unique "Instr" part of MAP from outlook *
' strFoldername = name of folder *
' For the use within this application normally "Network" and "InAccess" will be specified *
' The function returns boolean whether or not it was successful *
Dim colMaps As Outlook.Folders
Dim colFolders As Outlook.Folders
Dim fldFolder As Outlook.MAPIFolder
Dim fldMap As Outlook.MAPIFolder
' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If
Set colMaps = gnspNameSpace.Folders
For Each fldMap In colMaps
If InStr(fldMap.Name, strMapNameLike) > 0 Then
Set colFolders = fldMap.Folders
For Each fldFolder In colFolders
If fldFolder.Name = strFolderName Then
olItem.Move fldFolder
MoveMailItemTo = True
Exit Function
End If
Next fldFolder
MoveMailItemTo = False
End If
Next fldMap
End Function
Function MoveMailItemTo(strMapNameL
' This procedure moves the item to the specified Outlook mailbox folder. *
' That is identified by: *
' strMapNameLike = unique "Instr" part of MAP from outlook *
' strFoldername = name of folder *
' For the use within this application normally "Network" and "InAccess" will be specified *
' The function returns boolean whether or not it was successful *
Dim colMaps As Outlook.Folders
Dim colFolders As Outlook.Folders
Dim fldFolder As Outlook.MAPIFolder
Dim fldMap As Outlook.MAPIFolder
' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If
Set colMaps = gnspNameSpace.Folders
For Each fldMap In colMaps
If InStr(fldMap.Name, strMapNameLike) > 0 Then
Set colFolders = fldMap.Folders
For Each fldFolder In colFolders
If fldFolder.Name = strFolderName Then
olItem.Move fldFolder
MoveMailItemTo = True
Exit Function
End If
Next fldFolder
MoveMailItemTo = False
End If
Next fldMap
End Function
ASKER
jaffer,
I'm not sure if I'm following you on this. I want to move the emails based on a folder selection from a drop down box on the form. Can you help me modify your code to base the moving of the email on the selected folder instead of words in the subject line?
I'm not sure if I'm following you on this. I want to move the emails based on a folder selection from a drop down box on the form. Can you help me modify your code to base the moving of the email on the selected folder instead of words in the subject line?
Please give me a few more details to do it:
1- What is the name of your Source folder (Inbox ?), or if it is in a listbox, then what is the listbox NAME.
2- What is the name of your destination folder? , or if it is in a listbox, then what is the listbox NAME, and please tell me the path of it, for example:
is your Outlook personal profile called "Personal Folders"
is the destination folder directly under "Personal Folders", OR it is inside another folder like Inbox > MyFolder?
3- Do you want to move ALL the emails for the Source folder to the Destination folder?
OR do you want to read the subject or body, then decide if they contain a specific word, then move it?
OR do you want to move Unread emails?
I am just giving you a wider options.
jaffer
ASKER
1-The name of the source file is "Mailbox - TLMS Cost/Inbox". Please note that this is not my default mailbox but this mailbox is at the same level as my default mailbox.
2-The destination folders are in a list box. The name of the list box is "Assigned to Folder". The folders that the mail will be moved to is under the "Mailbox - TLMS Cost" mailbox which contains the Inbox and several other folders (like Mailbox - TLMS Cost > Problem Invoices).
3-I will be moving the emails individually (read the subject or body, then move it). Once the email is moved I will go to the next email and determine where it should be moved to. I will want the emails to remain unread after moving them.
2-The destination folders are in a list box. The name of the list box is "Assigned to Folder". The folders that the mail will be moved to is under the "Mailbox - TLMS Cost" mailbox which contains the Inbox and several other folders (like Mailbox - TLMS Cost > Problem Invoices).
3-I will be moving the emails individually (read the subject or body, then move it). Once the email is moved I will go to the next email and determine where it should be moved to. I will want the emails to remain unread after moving them.
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
Set OlItems = OlFolder.Items
olMail.Move OlFolderTo
olMail.UnRead = True
This will move the email to your selected folder form your listbox, then assign it as unread.
However, I would suggest you have a bit of a different appraoch to do this:
1. List the subjects of your Inbox into a Listbox, where you can see the subjects of all emails,
2. next to it have the listbox which selects the destination folders,
3. next to it is the command button which will run the code above.
this way things will be clear.
jaffer
ps, it is mid night here, so I will only see your reply in the morning after 7 hours.
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
Set OlItems = OlFolder.Items
olMail.Move OlFolderTo
olMail.UnRead = True
This will move the email to your selected folder form your listbox, then assign it as unread.
However, I would suggest you have a bit of a different appraoch to do this:
1. List the subjects of your Inbox into a Listbox, where you can see the subjects of all emails,
2. next to it have the listbox which selects the destination folders,
3. next to it is the command button which will run the code above.
this way things will be clear.
jaffer
ps, it is mid night here, so I will only see your reply in the morning after 7 hours.
> Once the email is moved I will go to the next email
did you want the code to do this for you?
that's why I suggested the Listbox which contains the subjects or/and the email body
did you want the code to do this for you?
that's why I suggested the Listbox which contains the subjects or/and the email body
ASKER
jjafferr,
The code bombed out at the following line:
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
It says that it can't find the object. Any suggestions?
To answer your latter question, yes I would like it to go to the next item. Thanks for your help. Talk to you soon.
The code bombed out at the following line:
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
It says that it can't find the object. Any suggestions?
To answer your latter question, yes I would like it to go to the next item. Thanks for your help. Talk to you soon.
Hi cobiana
I spent a good amount of time reading your 3 questions on EE,
then after doing a lot of searching on the Internet, I found your question to Sue on www.outlook...
I guess it is apparant that the solution is NOT as simple I/you thought if we have too many variables, so here is what I need your help in, and how we can make it work.
1- the 1st code I gave you works fine, I tried it without problem, so we will go from there,
2- Outlook accepts:
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
but it will NOT accept: vvvvvvvvvvvvvvvvvvvvvvv this part is not accepted.
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
3- so we will have to hardcode these names in the code, rather than using the combobox,
So please tell me ALL the names you have in your combobox, which I will have to hard code then this will work.
I have seen Nico's sample, which is an excellent addition to the above code to get it do what is required.
The following code will move ALL your emails from your "Inbox" to "Problem Invoices" folder, and mark them as Unread, this is to demostrate that it is do-able.
please try this and see if it works:
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Problem Invoices")
Set OlItems = OlFolder.Items
For Each olMail In OlItems
olMail.Move OlFolderTo
olMail.UnRead = True
Next
For Each olMail In OlItems
olMail.Move OlFolderTo
olMail.UnRead = True
Next
jaffer
I spent a good amount of time reading your 3 questions on EE,
then after doing a lot of searching on the Internet, I found your question to Sue on www.outlook...
I guess it is apparant that the solution is NOT as simple I/you thought if we have too many variables, so here is what I need your help in, and how we can make it work.
1- the 1st code I gave you works fine, I tried it without problem, so we will go from there,
2- Outlook accepts:
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
but it will NOT accept: vvvvvvvvvvvvvvvvvvvvvvv this part is not accepted.
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders(" & [Assigned to Folder] &")
3- so we will have to hardcode these names in the code, rather than using the combobox,
So please tell me ALL the names you have in your combobox, which I will have to hard code then this will work.
I have seen Nico's sample, which is an excellent addition to the above code to get it do what is required.
The following code will move ALL your emails from your "Inbox" to "Problem Invoices" folder, and mark them as Unread, this is to demostrate that it is do-able.
please try this and see if it works:
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Problem Invoices")
Set OlItems = OlFolder.Items
For Each olMail In OlItems
olMail.Move OlFolderTo
olMail.UnRead = True
Next
For Each olMail In OlItems
olMail.Move OlFolderTo
olMail.UnRead = True
Next
jaffer
ASKER
jaffer,
Here are the folders that the emails could be moved to:
Donna Recker
Herman Lewis
Inbox
Julie Ellison
Julie Salyers
Lamont Sams
Melanie Stephens
Paper 4998 Invoices
Plaxo Backup
PROBLEM INVOICES
I did try it and it did work. The only problem is that it moved every email in the Inbox instead of just the one I was looking at.
Here are the folders that the emails could be moved to:
Donna Recker
Herman Lewis
Inbox
Julie Ellison
Julie Salyers
Lamont Sams
Melanie Stephens
Paper 4998 Invoices
Plaxo Backup
PROBLEM INVOICES
I did try it and it did work. The only problem is that it moved every email in the Inbox instead of just the one I was looking at.
Hi cobianna
another question,
Since Nico have provided you with an excellent sample mdb, can I implement your request in that sample, it will save you/me a lot of time.
jaffer
another question,
Since Nico have provided you with an excellent sample mdb, can I implement your request in that sample, it will save you/me a lot of time.
jaffer
ASKER
jaffer,
You can do what every you think will work. I do appreciate you help with this. I am going to up the points for you help. I will make sure that Nico gets points for his contribution also.
You can do what every you think will work. I do appreciate you help with this. I am going to up the points for you help. I will make sure that Nico gets points for his contribution also.
Hi cobianna
This is the working code (I have tested it), but it depends on Nico's mdb,
I have emailed Nico asking for permission to include this code in his mdb and sending to you, after all it is his mdb,
so I won't be able to send you the mdb until I get his ok,
but since this code is mine, I am posting it for you here, and you can see where I am refering to Nico's Form.
Private Sub cmdMoveEmail_Click()
On Error GoTo Err_cmdMoveEmail_Click
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
' The From folder, which is fixed to the Inbox
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
' The To folder
If [Assigned to Folder] = "Donna Recker" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Donna Recker")
ElseIf [Assigned to Folder] = "Herman Lewis" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Herman Lewis")
ElseIf [Assigned to Folder] = "Inbox" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
ElseIf [Assigned to Folder] = "Julie Ellison" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Julie Ellison")
ElseIf [Assigned to Folder] = "Julie Salyers" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Julie Salyers")
ElseIf [Assigned to Folder] = "Lamont Sams" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Lamont Sams")
ElseIf [Assigned to Folder] = "Melanie Stephens" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Melanie Stephens")
ElseIf [Assigned to Folder] = "Paper 4998 Invoices" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Paper 4998 Invoices")
ElseIf [Assigned to Folder] = "Plaxo Backup" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Plaxo Backup")
ElseIf [Assigned to Folder] = "PROBLEM INVOICES" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("PROBLEM INVOICES")
End If
Set OlItems = OlFolder.Items
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.Move OlFolderTo
' olMail.UnRead = True
GoTo ExitFor1
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
ExitFor1:
' Mark as unRead
Set OlItems = OlFolderTo.Items
For Each olMail In OlItems
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.UnRead = True
GoTo ExitFor2
End If
Next
ExitFor2:
CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
Forms!frmMainMail!sfrmMail
Exit_cmdMoveEmail_Click:
Exit Sub
Err_cmdMoveEmail_Click:
MsgBox Err.Description
Resume Exit_cmdMoveEmail_Click
End Sub
jaffer
This is the working code (I have tested it), but it depends on Nico's mdb,
I have emailed Nico asking for permission to include this code in his mdb and sending to you, after all it is his mdb,
so I won't be able to send you the mdb until I get his ok,
but since this code is mine, I am posting it for you here, and you can see where I am refering to Nico's Form.
Private Sub cmdMoveEmail_Click()
On Error GoTo Err_cmdMoveEmail_Click
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
' The From folder, which is fixed to the Inbox
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
' The To folder
If [Assigned to Folder] = "Donna Recker" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Donna Recker")
ElseIf [Assigned to Folder] = "Herman Lewis" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Herman Lewis")
ElseIf [Assigned to Folder] = "Inbox" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
ElseIf [Assigned to Folder] = "Julie Ellison" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Julie Ellison")
ElseIf [Assigned to Folder] = "Julie Salyers" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Julie Salyers")
ElseIf [Assigned to Folder] = "Lamont Sams" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Lamont Sams")
ElseIf [Assigned to Folder] = "Melanie Stephens" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Melanie Stephens")
ElseIf [Assigned to Folder] = "Paper 4998 Invoices" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Paper 4998 Invoices")
ElseIf [Assigned to Folder] = "Plaxo Backup" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Plaxo Backup")
ElseIf [Assigned to Folder] = "PROBLEM INVOICES" Then
Set OlFolderTo = OlMapi.Folders("Mailbox - TLMS Cost").Folders("PROBLEM INVOICES")
End If
Set OlItems = OlFolder.Items
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.Move OlFolderTo
' olMail.UnRead = True
GoTo ExitFor1
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
ExitFor1:
' Mark as unRead
Set OlItems = OlFolderTo.Items
For Each olMail In OlItems
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.UnRead = True
GoTo ExitFor2
End If
Next
ExitFor2:
CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
Forms!frmMainMail!sfrmMail
Exit_cmdMoveEmail_Click:
Exit Sub
Err_cmdMoveEmail_Click:
MsgBox Err.Description
Resume Exit_cmdMoveEmail_Click
End Sub
jaffer
ASKER
jaffer,
we are getting so close. This code is connecting to my Outlook but I get a pop up message that "Can't move this item". Any suggestions?
we are getting so close. This code is connecting to my Outlook but I get a pop up message that "Can't move this item". Any suggestions?
cobianna
This code is not really good without the rest of the Form,
so if I don't get a reply till tomorrow (7 hours from now), I will do my own mdb,
if your Outlook Folder structure is:
- Mailbox - TLMS Cost
Deleted Items
Drafts
Junk E-mail
Sent Items
Donna Recker
Herman Lewis
Inbox
Julie Ellison
Julie Salyers
Lamont Sams
Melanie Stephens
Paper 4998 Invoices
Plaxo Backup
PROBLEM INVOICES
then this code will work, as it IS working on my PC.
jaffer
This code is not really good without the rest of the Form,
so if I don't get a reply till tomorrow (7 hours from now), I will do my own mdb,
if your Outlook Folder structure is:
- Mailbox - TLMS Cost
Deleted Items
Drafts
Junk E-mail
Sent Items
Donna Recker
Herman Lewis
Inbox
Julie Ellison
Julie Salyers
Lamont Sams
Melanie Stephens
Paper 4998 Invoices
Plaxo Backup
PROBLEM INVOICES
then this code will work, as it IS working on my PC.
jaffer
ASKER
I will wait to hear back from you on this.
cobianna
Not to let you wait too long, Nico have accepted using his mdb,
since this was bugging me, I couldn't sleep, its 2am, and I was checking my email every 30 minutes :o(
here is the download link:
http://s42.yousendit.com/d.aspx?id=1UG699FU9KG3T0YDQ7C6I6NF72
I will post this on the other Q too to acknoweldge Nico's guesture, where you can awar him points too.
follow the 3 steps as mentioned in his frmMainMail,
then when all the emails are in the subform,
select the from the "To Folder" you destination folder,
then click/select a record from the subform, and click on the Move mail command button,
this will move that email from the Inbox to your destination directory, and remove it from your subform too (give it a second or 2 to update).
Good luck, and please let me know in this Q how it goes.
Good night.
jaffer
Not to let you wait too long, Nico have accepted using his mdb,
since this was bugging me, I couldn't sleep, its 2am, and I was checking my email every 30 minutes :o(
here is the download link:
http://s42.yousendit.com/d.aspx?id=1UG699FU9KG3T0YDQ7C6I6NF72
I will post this on the other Q too to acknoweldge Nico's guesture, where you can awar him points too.
follow the 3 steps as mentioned in his frmMainMail,
then when all the emails are in the subform,
select the from the "To Folder" you destination folder,
then click/select a record from the subform, and click on the Move mail command button,
this will move that email from the Inbox to your destination directory, and remove it from your subform too (give it a second or 2 to update).
Good luck, and please let me know in this Q how it goes.
Good night.
jaffer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jaffer,
You are awesome. It appears to be working. The only problem I have is that this database is suppossed to function as a log of emails coming into the TLMS Cost mailbox. I did notice that when I move an email out of the Inbox and into one of the folders, that item drops off my table ("Mail" table). Is there anyway to have a table or query log the emails without deleting them after an email has been moved? I obviously will need this table/query to update everytime the "Get New Emails and update the table" button is initiated. I also will need to avoid duplicating items when the update occurs.
You have been a great help. I am going to award you with 750 points and will offer another 250 if you can address the above question.
You are awesome. It appears to be working. The only problem I have is that this database is suppossed to function as a log of emails coming into the TLMS Cost mailbox. I did notice that when I move an email out of the Inbox and into one of the folders, that item drops off my table ("Mail" table). Is there anyway to have a table or query log the emails without deleting them after an email has been moved? I obviously will need this table/query to update everytime the "Get New Emails and update the table" button is initiated. I also will need to avoid duplicating items when the update occurs.
You have been a great help. I am going to award you with 750 points and will offer another 250 if you can address the above question.
Yes possible
Add a new field to the Mail Table, call it HideMoved and make its Data Type as Yes/No, with a Default value as 0,
Then make the Subforms Record Source as a query instead of the Mail Table,
Add the field HideMoved to it, and in the criteria column, write 0,
then in the code, change this line:
CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
to
UPDATE HideMoved to -1 and offcourse use WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
thus the subform will not delete the record, but will not show it.
if you need further help let me know.
jaffer
> I will make sure that Nico gets points for his contribution also.
I think you missed/forgot this, so I have requested Community Support to split the points in the other Q.
jaffer
ASKER
You lost me at the changing the code part. What do you want me to change CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
1.
Add a new field to the Mail Table, call it HideMoved and make its Data Type as Yes/No, with a Default value as 0,
2.
change this line:
CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
to
CurrentDb.Execute ("UPDATE Mail SET HideMoved = -1 WHERE WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
3.
and the Record Source of sfrmMail should be:
SELECT EntryID, ReceivedTime, Subject, SenderName, CC, Body, Attachments, Action, ActionBy, HideMoved
FROM Mail
WHERE HideMoved=0;
4.
> I also will need to avoid duplicating items when the update occurs
It's built in Nico's mdb (that's why he is 5 times a genius, and I am not even 1 :o)
jaffer
Add a new field to the Mail Table, call it HideMoved and make its Data Type as Yes/No, with a Default value as 0,
2.
change this line:
CurrentDb.Execute ("DELETE * FROM Mail WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
to
CurrentDb.Execute ("UPDATE Mail SET HideMoved = -1 WHERE WHERE EntryID = '" & Forms!frmMainMail!sfrmMail
3.
and the Record Source of sfrmMail should be:
SELECT EntryID, ReceivedTime, Subject, SenderName, CC, Body, Attachments, Action, ActionBy, HideMoved
FROM Mail
WHERE HideMoved=0;
4.
> I also will need to avoid duplicating items when the update occurs
It's built in Nico's mdb (that's why he is 5 times a genius, and I am not even 1 :o)
jaffer
you will have to drag and drop HideMoved to sfrmMail (you can hide the column later, but you can use it manually too, so if you don't want to see an email, just click/check HideMoved, thus it won't show the next time the Form opens/refreshes)
this is the newer mdb
http://s49.yousendit.com/d.aspx?id=17U8ISU1BIKMU3FDV21P1KIQE6
http://s49.yousendit.com/d.aspx?id=17U8ISU1BIKMU3FDV21P1KIQE6
Hi cobianna
Did it work as it should?
jaffer
Did it work as it should?
jaffer
ASKER
I think we got it. Thanks for your help.
I have another question but I want you to get points for it if you are able to help me. My question centers around the message box that Outlook pops up when I get and move emails. It extremely annoying. I'm hoping someone has found code that will "yes" through the prompts. I probably will post the question tonight from home. Let me know if you have any ideas.
I have another question but I want you to get points for it if you are able to help me. My question centers around the message box that Outlook pops up when I get and move emails. It extremely annoying. I'm hoping someone has found code that will "yes" through the prompts. I probably will post the question tonight from home. Let me know if you have any ideas.
You are welcome.
Here you go
http://www.snapfiles.com/get/clickyes.html
Good luck in your project.
jaffer
cobianna
This is the final revision to the code (tested as working),
instead of hardcoding the folder names, now they are selected directly from the Form to the code, thus dynamic, thanks to BlueDevilFan
https://www.experts-exchange.com/questions/21809734/Change-folder-name-dynamically.html#16441129
here is the final code:
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Dim strContainer As String
Dim strFolder As String
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
' The From folder, which is fixed to the Inbox
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
strContainer = [cmbMap]
strFolder = [Assigned to Folder]
Set OlFolderTo = OlMapi.Folders(strContaine
Set OlItems = OlFolder.Items
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.Move OlFolderTo
' olMail.UnRead = True
GoTo ExitFor1
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
ExitFor1:
' Mark as unRead
Set OlItems = OlFolderTo.Items
For Each olMail In OlItems
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.UnRead = True
GoTo ExitFor2
End If
Next
ExitFor2:
Forms!frmMainMail!sfrmMail
Forms!frmMainMail!sfrmMail
and here is the download link:
http://s48.yousendit.com/d.aspx?id=3W422WFX77VP61LYKHHM3OLG9Q
jaffer
This is the final revision to the code (tested as working),
instead of hardcoding the folder names, now they are selected directly from the Form to the code, thus dynamic, thanks to BlueDevilFan
https://www.experts-exchange.com/questions/21809734/Change-folder-name-dynamically.html#16441129
here is the final code:
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlFolderTo As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Dim strContainer As String
Dim strFolder As String
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
' The From folder, which is fixed to the Inbox
Set OlFolder = OlMapi.Folders("Mailbox - TLMS Cost").Folders("Inbox")
strContainer = [cmbMap]
strFolder = [Assigned to Folder]
Set OlFolderTo = OlMapi.Folders(strContaine
Set OlItems = OlFolder.Items
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.Move OlFolderTo
' olMail.UnRead = True
GoTo ExitFor1
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
ExitFor1:
' Mark as unRead
Set OlItems = OlFolderTo.Items
For Each olMail In OlItems
If olMail.Subject = Forms!frmMainMail!sfrmMail
olMail.Body = Forms!frmMainMail!sfrmMail
olMail.UnRead = True
GoTo ExitFor2
End If
Next
ExitFor2:
Forms!frmMainMail!sfrmMail
Forms!frmMainMail!sfrmMail
and here is the download link:
http://s48.yousendit.com/d.aspx?id=3W422WFX77VP61LYKHHM3OLG9Q
jaffer
cobianna
I last change, you were looking for this in one of your open questions, Attachments.
Now this will tell you the names of your attachments, seperated with a comma, all in the same subform
http://s50.yousendit.com/d.aspx?id=147FIIEBE61WX2KHEZD7FHU42Z
jaffer
I last change, you were looking for this in one of your open questions, Attachments.
Now this will tell you the names of your attachments, seperated with a comma, all in the same subform
http://s50.yousendit.com/d.aspx?id=147FIIEBE61WX2KHEZD7FHU42Z
jaffer
ASKER
Is it possible to make the name of the files a hyperlink to the actual files?
ASKER
I am getting the following error message:
Error: -2147287037
Cannot save attachment.
Any suggestions?
Error: -2147287037
Cannot save attachment.
Any suggestions?
> I am getting the following error message
I think you forgot to make the c:\temp folder
https://www.experts-exchange.com/questions/21807451/Move-email-from-within-Access.html#16431957
> Is it possible to make the name of the files a hyperlink to the actual files?
you mean, when you click on it, it should open?
ASKER
I have the folder set up. I did notice that the emails are not brought in when this error occurs.
On the second question, yes, I want to be able to click on the attachment name and have the file saved in C:\temp folder.
On the second question, yes, I want to be able to click on the attachment name and have the file saved in C:\temp folder.
> I did notice that the emails are not brought in when this error occurs.
did it work with the previous versions of this mdb?
I will have to do a bit of testing on the hyperlink part, but i know I can get it to work
ASKER
Thanks. I will wait to hear back from you.
ASKER
I discovered something that may be causing the error. I noticed that some emails have Outlook emails as an attachment. For example, someone is attaching emails from a week ago to their current emails. These attached emails have attachments also. Is there a way to incorporate or exclude these types of emails?
can you explain this a bit more, please.
ASKER
Sure. I have an email in the TLMS Cost mailbox that has 3 attachments. One is a .bmp and the other two are emails. What I mean is that they are actual email messages that have been included as an attachment. To make issues more confusing, each of the emal messages has .bmp files attached to them.
I hope this makes more sense. If not, I will try to explain further.
I hope this makes more sense. If not, I will try to explain further.
Clear enough, I have seen them before,
but my reaction is
8-)
S-)
maybe we can trap the error, that will be next after the hyperlink,
in fact the hyperlink is straight forward, but the trick is to read the full file name between the commas :o)
but my reaction is
8-)
S-)
maybe we can trap the error, that will be next after the hyperlink,
in fact the hyperlink is straight forward, but the trick is to read the full file name between the commas :o)
ASKER
I think you have come to realize that my questions never have easy and straight forward answers. I have full confidence in you. :-)
:o)
Yeah, but maybe after I take my morning shower and have a nice warm cup of tea, then my brain cells "might" work better :o)
Yeah, but maybe after I take my morning shower and have a nice warm cup of tea, then my brain cells "might" work better :o)
ASKER
Were are you located.
Oman, in the Persian Gulf
between the Mountains, sea, oasis, desert, and a lot of sun shine
between the Mountains, sea, oasis, desert, and a lot of sun shine
ASKER
Sounds nice. I'm in Ohio. It's been a long time since I've been to that part of the world. I used to live in Greece.
change is good,
I travel a lot mostly to Europe, in fact I have a trip coming in about 2 weeks,
so nice to get some cold wind blow on the face :o)
ASKER
Chang is good. I plan on taking my wife to Europe for our 5 year anniversary next year. Well enjoy your trip to Europe. I'm going to bed. I will look for your comments in the morning. Have a good day.
thanks you too
1.
> Is it possible to make the name of the files a hyperlink to the actual files?
Due to the way the attachment names are stored in the "AttachmentsName" control (Name1,Name2,Name3,Name4,.
so what I did was, to double click any part of the file name, for example:
if your file names are:
fly high.bmp,sky.jpg,assignmen
and you wanted to open "assignments to do.txt"
then DOUBLE CLICK on any part of this file name, and the file will open with it's associated program,
Here is the code that does this:
Private Sub AttachmentsName_DblClick(C
On Error GoTo AttachmentsName_DblClick_E
' set the focus on the control
Me.AttachmentsName.SetFocu
' look for the location of the Comma to the Right of the click
' start looking from the clicked position
Rcomma = InStr(Me.AttachmentsName.S
' look for the location of the Comma to the Left of the click
' start looking from the clicked position
Lcomma = InStrRev(Me.AttachmentsNam
' start the file name selection form the Left
Me.AttachmentsName.SelStar
' to decide the how long the selected Text is,
' do we have a comma at the Right?
If Rcomma > 0 Then
' yes, so the selection should be between the commas
Me.AttachmentsName.SelLeng
Else
' no, so selet to the end
Me.AttachmentsName.SelLeng
End If
' open the application with the default program,
' keeping in mind, the attachment is saved in C:\Temp
Application.FollowHyperlin
Exit Sub
AttachmentsName_DblClick_E
MsgBox Err.Number & vbCrLf & Err.Description
End Sub
and here is the file:
http://s50.yousendit.com/d.aspx?id=3RZQIHQ33MBUD1MWCEXPOUBKQC
2.
> I have an email in the TLMS Cost mailbox that has 3 attachments
These attachments will be saved on c:\Temp too, and will show in the Form,
double clicking on them will open them with their attachments,
but the code will not show how many attachments each one have, nor it will give their names.
One thing I noticed, but I didn't change (maybe you can do it is you wish, if you need help let me know),
if you get a daily email with the same attachment,
in the Form it will give the name correctly, but in c:\Temp, the old file will be replaced with the new file,
thus making you have only the last file.
in order to avoid this, I would recommend saving the attachments not only with their names, but also with their date,
so the file will be saved as:
14-4-2006 12:15:30-assignments to do.txt
which makes it unique, but in the Form show only "assignments to do.txt" to reduce confusion (confused already :o)
jaffer
> Is it possible to make the name of the files a hyperlink to the actual files?
Due to the way the attachment names are stored in the "AttachmentsName" control (Name1,Name2,Name3,Name4,.
so what I did was, to double click any part of the file name, for example:
if your file names are:
fly high.bmp,sky.jpg,assignmen
and you wanted to open "assignments to do.txt"
then DOUBLE CLICK on any part of this file name, and the file will open with it's associated program,
Here is the code that does this:
Private Sub AttachmentsName_DblClick(C
On Error GoTo AttachmentsName_DblClick_E
' set the focus on the control
Me.AttachmentsName.SetFocu
' look for the location of the Comma to the Right of the click
' start looking from the clicked position
Rcomma = InStr(Me.AttachmentsName.S
' look for the location of the Comma to the Left of the click
' start looking from the clicked position
Lcomma = InStrRev(Me.AttachmentsNam
' start the file name selection form the Left
Me.AttachmentsName.SelStar
' to decide the how long the selected Text is,
' do we have a comma at the Right?
If Rcomma > 0 Then
' yes, so the selection should be between the commas
Me.AttachmentsName.SelLeng
Else
' no, so selet to the end
Me.AttachmentsName.SelLeng
End If
' open the application with the default program,
' keeping in mind, the attachment is saved in C:\Temp
Application.FollowHyperlin
Exit Sub
AttachmentsName_DblClick_E
MsgBox Err.Number & vbCrLf & Err.Description
End Sub
and here is the file:
http://s50.yousendit.com/d.aspx?id=3RZQIHQ33MBUD1MWCEXPOUBKQC
2.
> I have an email in the TLMS Cost mailbox that has 3 attachments
These attachments will be saved on c:\Temp too, and will show in the Form,
double clicking on them will open them with their attachments,
but the code will not show how many attachments each one have, nor it will give their names.
One thing I noticed, but I didn't change (maybe you can do it is you wish, if you need help let me know),
if you get a daily email with the same attachment,
in the Form it will give the name correctly, but in c:\Temp, the old file will be replaced with the new file,
thus making you have only the last file.
in order to avoid this, I would recommend saving the attachments not only with their names, but also with their date,
so the file will be saved as:
14-4-2006 12:15:30-assignments to do.txt
which makes it unique, but in the Form show only "assignments to do.txt" to reduce confusion (confused already :o)
jaffer
ASKER
Jaffer,
I'm getting a message that "Rcomma" variable is not defined.
Rcomma = InStr(Me.AttachmentsName.S
I'm getting a message that "Rcomma" variable is not defined.
Rcomma = InStr(Me.AttachmentsName.S
Ah, my old habbit
Dim Rcomma, Lcomma as string
Dim Rcomma, Lcomma as string
1. place this code on a command button
2. strMyString is the string you want to find in the Subject
3. the folder structure assumed is:
"Personal Folders" > "Inbox" > "inInbox" (this is where we will move the items from the Inbox)
4. I had to repeat the loop 2 times, because I noticed it didn't pick all the items the 1st time.
' Make Reference to Outlook Object Library xx.xx
Dim olApp As Outlook.Application
Dim OlMapi As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim olMail As Object
Dim OlItems As Outlook.Items
Set olApp = CreateObject("Outlook.Appl
Set OlMapi = olApp.GetNamespace("MAPI")
Set OlFolder = OlMapi.Folders("Personal Folders").Folders("Inbox")
Set OlFolderTo = OlMapi.Folders("Personal Folders").Folders("Inbox")
Set OlItems = OlFolder.Items
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If InStr(olMail.Subject, strMyString) > 0 Then
olMail.Move OlFolderTo
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
For Each olMail In OlItems
'' If olMail.UnRead = True Then
If InStr(olMail.Subject, strMyString) > 0 Then
olMail.Move OlFolderTo
' olMail.Delete
' ProcessMail = True
'' olMail.UnRead = False 'Mark mail as read, if that's necessary !?
End If 'InStr
'' End If 'UnRead
Next
jaffer