​In this VBA quick Tip we are looking at emailing from excel again but this time we are going to be looking at how to send a range from excel. The code below is the code you will need to copy so that you can do this. It is a lot different from the old code but some parts of it are the same. The first part of the code is selecting what range to send I have it set to A1:E20 and under that you will see an error message we have made just in case the range is wrong. 

​The next couple of parts of code all to do with creating the document that will be sent. 

It saves the workbook with a time and date stamp in the title after it has been sent, it will be deleted from your hard disk. If you would like to stop this look for the line of code being in with kill and remove it.

​The last part of the code is part which is actually going to send the email. This part is very similar to my other blog post about emailing with VBA. Here is where you choose you want it sent to and if you want anyone copied in. That’s all the code you will need to send a range of a spreadsheet to someone in an email.

When your procedure runs you may want to prompt the user fir information. The user might need to provide a value that is critical to a calculation or provide some other information. There are two ways you could prompt the user, the first is to use a InputBox unction and the second is to use the InputBox method.

The InputBox function works and is very similar to the MsgBox when it is used as a function. In the case of the InputBox function the value that is returned is the value that is giving by the user. That value is then used elsewhere in the procedure. A statement using the InputBox function takes the form.
​
varName = InputBox(Prompt, Title, Default, Xpos, Ypos, HelpFile, Context)
Prompt - (mandatory) the message that will display in the dialog box (up to 1,024) characters long)
Title - The text that appears in the dialog box title bar
Default – The default value that will display in the input box when it first appears. This should normally be provided as a guide for the user. If you omit Default the text box will be empty.
Xpos – The horizontal position of the dialog box from left edge of the screen (measured in points)
Ypos – The vertical position of the dialog box from the top of the screen (measured in points)
HelpFile – The help file used to display the custom help for this dialog box
Context – The number identifying the help in the help file.

Using the InputBox method is more versatile then the InputBox function. And allows you to specify the data type or types you want returned. It takes the structure:
​
varName = Application.InputBox(Prompt, Title, Default, Xpos, Ypos, HelpFile, Context)

The type argument at the end of the argument list determines the type of data that will be returned if you omit Type VBA assumes the data type will be text.

The various data types and their respective values are shown below.

0 – Formula
1 – Number
2 – Text (this is the default)
4 – Boolean
8 – Reference to a range
16 – Error value
32 – Array of Values

​This code below is an example of the InputBox Function. When you run this code a box will appear asking the user for the number of the month. It will have a default value of the current month it when display the month in cell D1. 

​The code below is an example of a InputBox method it is very similar to the one before except this lets you type in text so you can actually write the month in text and not just a number. If you try to entre text into the other code you will get an error message.

​In this tutorial I’m going to show you how to link Excel to Word. This will take your information out of excel and put it into word for you.  The first thing you are going to want to do is to make surer that word is selected in the referencing window. To do this in the VBA editor go to tools and then to References.

​That will bring up this window, and you want to search through the list until you find Microsoft Word and tick it you might also need to find excel for some reason it was not selected when I done this but it normally should be.

​This is the code you will need to use to make the List of names be copied from Excel to Word.

​If you run this code in Excel you will get this message box appear.

​Now if you open word you should see your names in there just like below.

​This VBA quick tip is going to show you how to generate the square and square root of a number. This first little bit of code will generate the square root of the test number which is 8 at the moment but you could have 9826 there it doesn’t matter.

When you run this code it will display a message box with the result in it just like below.

​Now the code below is very similar but this why will generate the square of the number. You can get the square of a number by using the ^ (as for formula in a cell).

​If you run that code you will get these message boxes appear one after the other.

​Today in this tutorial I’m going to be showing you how to create your own trim function in excel VBA, which you can then use in a Excel spreadsheet. The code you see below is the code you will be using now I have gone just for a normal trim function but you could edit the code to make it do more for example you might want it to add some punctuation to it. I have named it MyTrim so its easy to find but you can name it whatever you like.
​

​​Once you have written your code you can go try out your trim function. As you can see I have written it in C1 and set the text location for it to trim as A1. This will then display the trimmed result in C1. This will only work for one cell at a time and will come up with an error message if you try yo use a range.
​

​Today I am going to show you how to create a welcoming message to anyone who uses you spread sheet. First thing we are going to do is to create a userform with our message in it use the lable tool to put the message into the form.

​Once you have done that you will need to double click on the user form and it will take you to the place where you can write code for your user form. You will then need to add the code below to it if this is a new UserForm just delete the code that’s already there.

​The next step will be creating a new module by going to insert then down to Module. Once you are in the new Module then you will need to put this code in below. As the code simply puts it will unload the UserForm.

​This next art of code will need to go into the workbook and this is the code that will make the UserForm show whenever has been opened.

​You should be greeted when you open you workbook by something that looks like this.

​Today I will show you how to make a macro run a specific time and in specific time intervals. The code below is the code that will allow you to run a macro at a certain time of day and Excel will run the macro at the time you set as long as Excel is open. You will need to change the name of your macro unless it is name TimedMacro.

​The next set of code will show you how to make it so the macro will run 5 seconds or 15 minutes after the workbook has been opened. This is useful if you know you will need to run a macro a certain time after opening a workbook. I would suggest using this if you have a macro that you will use every day.

​In this tutorial I will be showing you some code so that you will be able to create your own pivot table sing VBA. The code below is the code I got when I created my pivot table this means you are going to have to change a couple of things before it will work for you.

​The first part you are going to need to change the source data which is located in the red box below you are just going to need to change it to the range you want.

​The next thing you are going to need to change is the table destination, mine is going to be put into sheet2 but you might want to have in sheet1.

​The next part is not important but you might find it useful you might also want change the name of the pivot table rather than keeping it as PivotTable3. Now you should be able to run it and have a pivot table crated. 

This tutorial will show you the code you need to be able to send an email from excel. this is really easy and onlly takes a little bit of code which is displayed below.

As you can see this is a very simple code and you can copy and use it for your spreadsheet. You will have to remember to change a few things though and they are the email subject, email send from address, the send to address, the Cc address if you have one if not you can remove it from your code and the email body. The other thing you might have to change (probably wont need to) is the email application you will be using I use outlook so you can see that I have typed in "Outlook.Application" next to set email_object this will make it use outlook to send the email.

This code below will show you how to change its so that text is spread out over multiple column instead of just one. This code can be use to do this to many rows but it can only do it to one column at a time. From the code below there are a few things you will be able to change to suite your needs:

one_to_how_many_columns = Determines how many columns should be used as a gap to place the (split/converted) data when two consecutive columns are converted.
DataType = xlDelimited or xlFixedWidth (The text to column feature has two modes – Character Delimited and Fixed Width.)
TextQualifier = xlDoubleQuote or xlSingleQuote or xlNone
Tab = True or False
Semicolon = True or False
Comma = True or False
Space = True or False
TrailingMinusNumbers = True or False
ConsecutiveDelimiter = True or False

This is what your text will look like before:

And this is the code you will need to enter: 

Then you can run the code and your text will then look like this: