Outlook emails vba code

Spring cleaning the desktop, found this old vba code that I must have mashed together sometime back.

Well I tested it today and it works, so posting it here for future use.

What it does, collects every email for the GAL in outlook…

Sub GetAllGALMembers()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olGAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntries
Dim olMember As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olGAL = olNS.GetGlobalAddressList()

Set olEntry = olGAL.AddressEntries
On Error Resume Next
Open "d:\project\email\emails.csv" For Output As #1
Dim i As Long
For i = 1 To olEntry.Count
Set olMember = olEntry.Item(i)
If olMember.AddressEntryUserType = olExchangeUserAddressEntry Then
strName = olMember.Name
strAlias = olMember.GetExchangeUser.Alias
strAddress = olMember.GetExchangeUser.PrimarySmtpAddress
strPhone = olMember.GetExchangeUser.BusinessTelephoneNumber
strCity = olMember.GetExchangeUser.City
strCom = olMember.GetExchangeUser.CompanyName
strJobT = olMember.GetExchangeUser.JobTitle
strDepar = olMember.GetExchangeUser.Department
strOffLoc = olMember.GetExchangeUser.OfficeLocation
Print #1, strName & vbTab & " (" & strAlias & ") " & vbTab & strAddress & vbTab & strPhone & vbTab & strCity & vbTab & strCom & vbTab & strJobT & vbTab & strDepar & vbTab & strOffLoc
End If
' for testing
' If (i = 200) Then
'  GoTo 10
' End If
Next i

10 msgbox ('Done!!')

End Sub

 

Advertisement

Word to PDF

A week ago had a personal task to convert 40 or so word documents to pdf in standard and minimum size. Doing them by one at a time was felt such a waste, so recorded and edited a vba macro to do the same task in a jiffy.

Here’s the simple vba subroutine to do the conversion.

A Dynamic Dependent Drop Down List in Excel

A few days back needed an excel sheet which had dynamic dependent drop down list.

I had to choose one unit, based on that choose the second thing and based on the first two choose the next thing. Sounds easy. Go try.

Following two resources gave some glimmer of hope but it nose dived as soon as it met my requirement. But….

A Dynamic Dependent Drop Down List in Excel

Create Dependent Drop Down Lists

After half a day of fruitless experiments, and a night’s sleep,the problem was solved.

With a little combination excel vba and some formulae, got what I wanted.

So here’s my take on getting 3 dependent dynamic drop down in excel. Enjoy!!

The excel sheet can be downloaded from here!

Simple Code to Find Which Excel Row is Being Modified

Excel’s inbuilt functions are powerful. They can achieve a lot. Almost anything can be conquered with these functions.

Well almost……everything!!

Recently needed a function to know which row is being modified. As far as i know, there’s no way this can be achieved by using excel’s function. This is where little bit of vba helps.

Here’s the code that helped.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 Or Target.Column = 4) Then
        If Target.Row > 1 Then Cells(1, 6) = Target.Row
    End If
End Sub

In the above sheet change method, the code checks if any row has changed in the columns 3 and 4 and if something has changed beyond row 1 in these columns, the F1 cell is updated with the row number.

Simple and small code but very useful when one wants to know which row is getting modified in excel.

The information about the row being modified can be used to do lot of things using excel in built functions.

Now think where can one use this. Stay tuned I have a follow up post on the use.

Is There a Dinosaur Sitting on Your Excel File?

I am handing few big excel data files lately and these files are loaded with vlookups, sumifs,sumproduct and truckload of loopy VBA code.

Running the VBA code was pain, excel drags as if a dinosaur is sitting on it. The files were so muddled that even the excel in-built functions to convert a range of cells to number takes 5 mins.

Trying to optimise the vba application in the excel file, I did the usual Application.ScreenUpdating = False and Application.Calculation = xlManual in the VBA to speed up all macros. This helped but not entirely.

The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates and re-calculates each cell and resolves dependencies. Excel recalculate’s whenever an event occurs (for example a cell is changed, a VBA code calls for re calculation or anything else). For each change the entire calculation process is carried out, so the trick to gain major benefit came from looking at all the formulas and optimising them.

It’s not easy but there are many fantastic resources that can help. Here are some of my favourite ones.

75 Excel Speeding Tips
Its a long List, many repeated but worth a visit.

Optimize VBA
Excellent tips to optimize vba code. You should do this before you move onto anything.

How to speed up calculation and Improve performance of Excel and VBA
These are excellent tips and must visit to know more about the calculations steps followed by Excel.

Pareto Chart in Excel

Creating pareto chart in excel requires multiple steps as there is no direct method to accomplish this in excel. Here’s a vba a script that creates a pareto chart in excel.

How to use the script?
Select the data and run the ParetoChart sub. That’s it. The scripts always assumes the first row is headers. Don’t forget that.

And if you need Boxplot in excel, this is where you should go.

Here’s the code….

VBA Script to Add Auto Captions to Images In a Word document

Has this ever happened to you? You are writing a big image heavy test report or user document in Microsoft word. And as you get to the end of your job, you suddenly realise you forgot to caption your images in the document.

You get angry on yourself at the prospect of spending an extra hour to correct this oversight.

Well a similar thing happened recently. And when I thought about it, it had happened more than I want to reveal here. Since writing report isn’t a daily activity of my job, this thing reoccurs. After a hundred and odd page, I go back and add caption to the pictures that I forgot in the first place.

So this time, instead of fixing the captions, I spent the next hour experimenting, how to fix the mess and came up with the following VBA script to add auto captions to images in a word document.

Simple script. Posting it here in case if someone is in similar situation.

Sub CaptionPictures()
Dim objPic As InlineShape

For Each objPic In ActiveDocument.InlineShapes
objPic.Select
Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:="", _
Position:=wdCaptionPositionBelow, ExcludeLabel:=0
Next objPic
End Sub

Getting User Id of The Person Currently LoggedIn [vba]

Working on a side project in VBA, a few weeks ago, I needed a function to get the userid of the person currently logged in to the system. Here’s the function that came handy! Posting it here for the general audience!

 

Option Compare Text
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long</code>

Public Function NTDomainUserName() As String
' This function gets the user id of the person currently logged in
Dim strBuffer As String * 255
Dim lngBufferLength As Long
Dim lngRet As Long
Dim strTemp As String

lngBufferLength = 255
strBuffer = String(255, 0)
lngRet = GetUserName(strBuffer, lngBufferLength)
strTemp = UCase(Left(strBuffer, lngBufferLength - 1))

If strTemp = "" Then
NTDomainUserName = "Not Known"
Else
NTDomainUserName = strTemp
End If

End Function


' Test function

Sub test_Open()
UserId = NTDomainUserName
Sheets(1).Cells(1, 1) = UserId
End Sub

An Excel App for Your Brain!

While browsing through google play, this android app got my attention. Having discarded my android phone, I asked my friend N to download the app on his mobile.

Liked the game and then we two went for a walk to stretch our legs and give our eyes break from the computer screen.

It is in this walk that we challenged each other if we can create this simple game in our programming language of choice.

We figured by an hour we can complete the game. I wanted to do it in java but didn’t have the sdk installed on my system, so decided to use my old pal vba. Launched excel and began with my phone timer set to shout in an hour.

By stretching 5 mins more to an hour, I had the basic functionality ready in excel.

Then it took, another one hour to get this game little more polished. Though far from complete.

So here’s the result. Click to download the excel file and play

What I learnt from the exercise?
The application.ontime function in excel 2007 is broken. Was not able to stop it. So had to introduce a new Boolean variable to stop the play.

Working with shapes was a new thing for me. I have always used cells or a user form for working in excel VBA, but for this I wanted to see how to use shapes.

Not bad for a total of 2+ hours of effort and it refreshed my dormant vba skills!!

Lot can be improved in the app, but hey I have real work to do. If you want, you are free to refine the game further. or look at the code here.

Fortran DLL and Excel -a 4 step video tutorial

Ever since I had used the screen-o-matic screen-cast, I was itching to use it again to create something.

Today morning finding some time, I decided to go ahead, but then stumbled on these awesome four step video tutorials on mixing fortran and excel.

This is what I was going to screen-cast, but this videos do an excellent job.

So please enjoy and learn.

Step 1

Step 2

Step 3

Step 4

Fortran DLL and Excel – How to mesh them together

Making a fortran DLL and using it with excel has two distinct advantage.

1. We don’t need to re write the fortran code to a vb code.

2. Fortran DLL is significantly faster than the vba code.

And with all this we get goodness of excel with the power of fortran.

It is with this idea, I had created few fortran dll’s over the years and used them with excel in my professional and personal projects.

The graphics and data handling capability coupled with fortran’s numerical engine had served me well.

If you are interested in how to mesh the two, the following posts should be useful to you.

Fortran and Excel – good fit for marriage

How to create fortran DLL with intel compiler using Visual Studio?

How to create fortran DLL with intel compiler without visual studio?

How to create fortran DLL with gfortran open source compiler?

On Wasting time and inefficiency

From the new rules blog

Wasting time and inefficiencies are the way to discovery. When Condé Nast’s editorial director Alexander Liberman was challenged on his inefficiencies in producing world-class magazines such The New Yorker, Vanity Fair, and Architectural Digest, he said it best: “I believe in waste. Waste is very important in creativity.”

Out of many things, inefficiencies have helped me learn java and pgplot at a faster rate.

Trying things, creating silly programs has produced so many wasteful programs, but each has sharpened the skills.

A habit and International Standard Atmosphere

Now it’s a habit.

If you are into programming then you might be all too familiar with the first hello world program that we all start our learning with.

This was true for me till I got into aerospace, now my first program is always the program to calculate temperature at an altitude for international standard atmosphere.

This has become a habit. I do this for all the new programming languages that I learnt. Perl, python, VBA, vb, java, etc you name it and my first program in all this language was ISA.

The other day I was browsing my old desktop and found this long forgotten visual basic excel program that I wrote 6+ years back. It’s a small program that calculates and gives temperature and pressure at different altitude according to international standard atmosphere.

Download the excel file here. ISA is coded as UDF in excel.

BoxPlot in Excel

Two years back around this time, while working with matlab, I came across the plot called boxplot.

Liked the idea of. Ever since I wanted to replicate that in one my fav language of choice, fortran.

Well that became part of my work.

While reasearhing boxplot on google, I came across this page. Loved the idea of creating boxplots in excel.

So coded this excel VBA macro that takes in some numbers and creates a boxplot for it.

The video below shows how this works.

Download the excel file and don’t forget to look under the hood.

Download and play with Boxplot in Excel

or for those who don’t want to download, here’s the complete code.

Count 1 to 10 game for Kids in Excel

In the April of last year, my then 2 years old daughter learnt  counting from 1 to 10. Just after a week when she first began saying one, two, three…. I recorded her voice.

Then I taught her to count her fingers, she enjoyed it so much that she always wanted me to play with her by counting her fingers… 

This gave an idea. And this download attached to this post is the result of it.

Its excel version of the game that I used to play with my daughter. The excel game has my daughter’s voice and her little fingers.

She loved this game and we had  ton of fun with it. But now she has moved on to other stuffs, so I thought I will post these files here so that anyone with kid around that age can use the excel file.

The code is in VBA. You need to enable macros and have your speaker on to enjoy the game.

Hope you all enjoy this as much as myself and Anmol did.

Instructions:

1. Download and unzip the zip file.
2. Click on the excel file
3. Enable macro
4. Use your mouse and start clicking on Anmol’s little fingers.

 

And yes, its unlocked so you can learn some cool sound playing in vba too

Cheers

Simple Excel game to help your kids learn numbers

Today developed a prototype simple game for my daughter in excel with her help!!

The concept is simple.

It has button to click and when you click it, it shows one number which iterates from 1 to 10.

It has my daughters voice and each click her voice says the letter.

Main purpose of the game was to help her learn numbers. She loves listening to her voice.

Will share the same with you all in a week or so.

The simple excel file can help your kids have ton of fun and learn numbers too.

Cheers