98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA
Excel VBA
Resolved · Medium Priority · Version 2016
Tue 21 Aug 2018, 17:04Delegate Dimitri said...
Dimitri has attended:
Excel VBA Advanced course
Excel VBA
How can I move a selected email to a Folder using vba ?
Thank you very much
For more information, check out our Excel data analysis course London.
Wed 22 Aug 2018, 11:14Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
Thank you for the forum question.
The code below will copy and paste all selected emails to a folder and then delete the emails.
Sub ArchiveItems()
' Moves each of the selected items on the screen to an folder.
Dim olApp As New Outlook.Application
Dim olExp As Outlook.Explorer
Dim olSel As Outlook.Selection
Dim olNameSpace As Outlook.NameSpace
Dim strName As String
Dim olFolder As String
Dim intItem As Integer
Set olExp = olApp.ActiveExplorer
Set olSel = olExp.Selection
Set olNameSpace = olApp.GetNamespace("MAPI")
olFolder = "C:\Users\Jens\Documents\EmailFolder\"
For intItem = 1 To olSel.Count
strName = olSel.Item(intItem).Subject
olSel.Item(intItem).SaveAs olFolder & strName & ".msg", olMSG
olSel.Item(intItem).Delete
Next intItem
End Sub
The code below will move selected emails to a folder within Outlook
Sub ArchiveItems()
' Moves each of the selected items on the screen to an Archive folder.
Dim olApp As New Outlook.Application
Dim olExp As Outlook.Explorer
Dim olSel As Outlook.Selection
Dim olNameSpace As Outlook.NameSpace
Dim olArchive As Outlook.Folder
Dim intItem As Integer
Set olExp = olApp.ActiveExplorer
Set olSel = olExp.Selection
Set olNameSpace = olApp.GetNamespace("MAPI")
' This assumes that you have an Inbox subfolder named Archive.
Set olArchive = olNameSpace.GetDefaultFolder(olFolderInbox).Folders("Archive")
For intItem = 1 To olSel.Count
olSel.Item(intItem).Move olArchive
Next intItem
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 22 Aug 2018, 11:55Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
The code I gave you has to be put in the visual basic editor in Outlook.
Please let me know if it is not what you expect.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 22 Aug 2018, 17:50Delegate Dimitri said...
RE: Excel VBA
Dear Jens,
Thank you for your prompt feedback. This is exactly what I was looking for and it works perfectly well !
The only question I have left is:
If different Users are using this code (other than me I mean), there is a chance that their Outlook library is not activated in their Excel workbook. Thus they might get an error message when launching the procedure. Is there a way, we could force the Outlook library (or any other library)to be ticked automatically when starting the Macro ?
Thank you very much for your time and for your precious help !
best regards,
Dimitri
07 563 87 26 74
Thu 23 Aug 2018, 10:28Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
Please check the path to the library. In the Visual Basic Editor click Tools -> References and tick the Outlook Library. If you have a look at the bottom of the dialog box you will find the path. Please make sure the path is the same as my path or you have to change it.
You will need a macro and a function.
See code below:
Sub ActivateLibrary()
Const outlookRef As String = "C:\Program Files (x86)\Microsoft Office\Office16\MSOUTL.OLB"
If Not RefExists(outlookRef, "Microsoft Outlook 16.0 Object Library") Then
Application.VBE.ActiveVBProject.References.AddFromFile _
outlookRef
End If
End Sub
Function RefExists(refPath As String, refDescrip As String) As Boolean
'Returns true/false if a specified reference exists, based on LIKE comparison
' to reference.description.
Dim ref As Variant
Dim bExists As Boolean
'Assume the reference doesn't exist
bExists = False
For Each ref In Application.VBE.ActiveVBProject.References
If ref.Description Like refDescrip Then
RefExists = True
Exit Function
End If
Next
RefExists = bExists
End Function
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 23 Aug 2018, 21:45Delegate Dimitri said...
RE: Excel VBA
Dear Jens,
Thank you for your feedback again.
I cannot execute the code - to activate the Outlook library - because there is a VBA error at the following instruction:
For Each ref In Application.VBE.ActiveVBProject.References
The error is:
Method 'VBE' of object '_Application' failed (error 1004)
-----
Regarding your first code to send a selected email to a folder, it seems that the program cannot copy properly emails whose subject starts with: "RE:"
Similarly, if the subject contains the following character: "/" there is an error. I guess the only way of avoiding it is to read the content of the subject and remove any concerned sign/character before copying ?
the error occurs at the following instruction:
olSel.Item(intItem).SaveAs olFolder & strName & ".msg", olMSG
Best regards,
Dimitri
Thu 23 Aug 2018, 22:08Delegate Dimitri said...
RE: Excel VBA
Hi Jens,
I managed to solve my second issue regarding special characters (I did use a loop on the String variable to remove any special characters before copying the message). However I still do not know how to make the library activation work automatically... (see my previous message)
Thank you for your help
Fri 24 Aug 2018, 09:33Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
You can nest the replace function to replace characters which are forbidden in file names.
strName = Replace(Replace(olSel.Item(intItem).Subject, ":", ""), "\", "")
Forbidden characters in Windows:
< (less than)
> (greater than)
: (colon - sometimes works, but is actually NTFS Alternate Data Streams)
" (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
I will look into the issue about the reference to the library and will be back later today.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 24 Aug 2018, 14:55Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
Microsoft has changed security again.
You can only code to activate a library, if you in the trust center settings have ticked a box.
Excel: File -> Options, Trust Center. Trust Center -> Macro Settings. Is the "Trust access to the VBA project object model" ticked?
I have also tried to code it as late binding (no need of reference to library), but unfortunately without luck.
I will come back if I find a solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 24 Aug 2018, 15:33Delegate Dimitri said...
RE: Excel VBA
Hello Jens,
Thank you !
You are right: it works if the box is ticked.
If you can find a solution to code it I am still very interested.
Many thanks.
Regards,
Dimitri
Wed 12 Sep 2018, 11:38Delegate Dimitri said...
RE: Excel VBA
Good morning Jens,
I hope you are well.
I am trying to import a range of data from an Excel spreadsheet using a VBA code without opening the Excel spreadsheet. I would like to avoid setting a Connection for doing this.
More precisely:
The file (Workbook) I would like to copy the data from is: SourceFile
The path where this file is stored is: Path
I would like to create a VBA code to pick up the data from SourceFile using the Path name and put it into an Array(). I do not want SourceFile to be opened during the operation. That's it.
Do you have a piece of code to do it ?
Thank you very much.
Regards,
Dimitri
Wed 12 Sep 2018, 22:47Trainer Jens said...
RE: Excel VBA
Hi Dimitri,
Thank you for the forum question.
Can I please ask you to raise a new question, when you have a new question and not continue in an old string.Thanks.
You can use ADO but you have to create a connection string to the file (we did in on the Advanced VBA course to an Access database). When you have the data in a recordset it is easy to transfer them to an array, but the recordset will also be stored in your computer's memory so you will probably not need the array.
The code below is an example of ADO connection string to workbook.
Dim strSQL As String, conStr as String
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & path & "';" & _
"Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$] WHERE [Thing1] > 1"
cnn.open conStr
rs.Open query, cnn, adOpenStatic, adLockOptimistic, adCmdText
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating a range of monthly payments as textYou could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle. |
