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


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?

    Dinosaur Sitting On your excel App
    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

    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….

    Continue reading

    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
    Selection.InsertCaption Label:="Figure", TitleAutoText:="", Title:="", _
    Position:=wdCaptionPositionBelow, ExcludeLabel:=0
    Next objPic
    End Sub