excel vba

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA

Excel VBA

resolvedResolved · Medium Priority · Version 2016

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.

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

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

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

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

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

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

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

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



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

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

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 courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel software training and other training courses.

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help


3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.