Category Archive 'Excel macros'

How I send Outlook Email Through Excel

Excel 2013, Excel macros

One of the main things that I like to do when automating excel files is getting the whole process done. What do I mean? Not only creating a file and saving it to a drive, but also sending it out to the group that I need it to get to on a daily basis.

For example, I have this file that creates a list of tennis players:

Suppose that I run this file every day, save it to a drive and send it to 4-5 people? I can do all of that (more or less) from my excel file. How? By helping Microsoft Excel communicate with Outlook.

It’s similar to what you’d do when building any other macro. I personally use 2 different steps:

#1-Add the “standard” email funfction to my file. For this to be done, I always copy the same text to my files:

Then, I can simply ad the relevant code to my existing macros. Here is a simplified version that would send out a file:

It’s fairly straightforward and it works:) Once I click the button, an email shows up and I can simply press “send”

You can download the spreadsheet here!

Transforming Addresses Into Label Formats With Excel

Excel macros, Excel spreadsheets (.xls)

Today, a reader sent me a list of 400 addresses that were all entered in a format like this one:

P.S: I am not publishing the actual file for privacy purposes and don’t feel like writing down 400 fake ones:)

Her hope was to transform the addresses into what you’d use on a label such as:

I thought this would be a good VBA practice. So what I wanted to do was use a “loop” function to go through each line and for each one, write down the label over 4 lines. How did I manage? Here is the code I used:

And the end result? Simple enough? You bet

Do Google Docs Scripts = MS Excel VBA?

Excel macros, Google Cloud Spreadsheets

There are many benefits to using Google Docs instead of Microsoft Excel which I’ve certainly discussed on this blog but one big downside in my opinion is/was the lack of complexity of Google docs. I run several excel spreadsheets that use complex functions, macros, etc.

Google Docs Scripts Could Be The Way Forward?

Google does offer ways to add complexity through “scripts” which I had heard about but had never used. I then ended up looking for ways to get the latest price of “Bitcoins” and while there were ways to do it with a simple formula, I did see a script which looked interesting so I decided to give it a try. I’ll tell you how step by step.

Step #1-Create A new spreadsheet

Step #2-Name a cell (this specific script would add the Bitcoin value into a specific cell so I needed to name a cell:

Step #3-Create A Script

Step #4-Run The Script

Voila! You can see the value here:

This is certainly interesting and I’ll without a doubt be reading up on these and trying several more. I doubt I’ll get rid of Excel anytime soon but if I’m able to move some of my spreadsheets, I’d be thrilled.

One big challenge that I expect is that I’ll certainly have a lot more trouble finding info about Google Doc scripts than Excel VBA.

Have any of you tried Google Docs scripts? If so, I’d love to hear from you.

Creating A CSV Output With An Excel Macro (Part 2)

Excel macros

In yesterday’s post, I created a very simple macro that would take a range of data on a given sheet and then save it as a csv on my computer. It’s a simple task but one that we often end up doing manually for no reason. If you missed that post, find it here:

Creating A CSV Output With An Excel Macro (Part 1)

Today, I wanted to add 2 very simple features:

#1-add today’s date in the name of the file

If you’re saving specific data, chances are that you’ll want or need to keep it all in a folder and be able to go back in time to see what the data was 1 or 2 days ago. Simple enough right? And yes, it’s very easy to do even in excel. How? Look at the original code that we used:

******************************************
Sub Macro1()
Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”C:\Book2.csv”, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

******************************************

To do this, I’ll simply add a few more lines:

******************************************
Sub Macro1()

date1 = Date
date1 = Format(date1, “YYYYMMDD”)

Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”L:\DOC_” & date1 & “.csv“, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

******************************************

This will help me save a file using the date in the name.

#2-add a button to easily run the macro

I’ve done this in a few past files but it’s a very recurring question so let’s do this. First I’ll go to the developer part of my Excel:

Then, I’d simply go to design mode and click insert, add a macro:

And assign that macro:

Simple enough?:) I hope so

Creating A CSV Output With An Excel Macro

Excel macros

In the past I’ve looked at introductions to excel macros. If you’re a beginner I recommend that you take a look at:

Excel Macros – What Are Excel macros
-Excel Macros – How To Build Excel Macros
Excel Macros – How To Build Excel Macros Part II

-Our Excel Beginner’s book

One of the more common things that I do with my work macros is the creation of files. So I’d have one big file where all of my data resides. Then, every day, I’ll need to either save to a separate file or send by email some of that data, sometimes in an excel file but even more commonly in a .csv format. There is very little difference between the 2 in any case.

Today, I thought I’d do a very small into to how that can be done. Automating this process reduces errors but also makes it much easier to do. This can be used to send data to a co-worker, a client, supplier, etc.

Suppose that you have a big spreadsheet with dozens of sheets and you need to save one specific range to a csv. How would you do it? Simple. You’d create a macro that looks a little like this:

******************************************
Sub Macro1()
Sheets(“YUA”).Select
Range(“A4:H7”).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir “C:\Users\cantinpe\Desktop”
ActiveWorkbook.SaveAs Filename:=”C:\Book2.csv”, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

******************************************

As you can imagine, this would take the sheet “YUA”, copy the specific range and save it to my C drive.

Other steps I could then do would be saving the file using today’s date and adding a button to make it even easier. I’ll try to do that in my next post:)