Using Named Ranges in Microsoft Excel

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" width="320" /></a></div><br /><br />You can view the complete tutorial here <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">How to Use Named Ranges in Microsoft Excel </a>.<br /><br />When using Named Ranges there are also set of rules which you need to know like the scope where you can use the specified named range. If it's within only a single worksheet or the entire workbook.<br /><br />Check this out <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">Named Range Rules</a> and be totally aware of the do's and don'ts when using this functions.<br /><br /><b><span style="color: #cc0000; font-size: large;">Managing Your Named Ranges</span></b><br /><b><br /></b>There'll come a time when you want to edit or delete a Named Range. To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" width="320" /></a></div><br />From here you can Edit and Delete your Named Ranges, or even create new ones. Remember, once you delete a name you cannot undo that action.<br /><br /><br /><b><span style="color: #cc0000; font-size: large;">Different uses for Named Ranges in Excel:</span></b><br /><b><span style="font-size: large;"><br /></span></b>1) <b>Formula References:</b> Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas<br /><br />2) <b>Multiple Print Areas</b> on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges. Note: Excel 2007 onward allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area.<br /><br />3) <b>Reduce worksheet clutter</b> with Named Constants. Sounds complicated but it's not. A Named Constant is just a fancy name given to values you might use repeatedly in your formulas. For example, let's say you're planning to have a year-end inventory sale and wanted to give a 10% discount on selected products, you could give the value "10%" a name like "Discount" and then you could:<br /><br /><ul><li>Write your formulas like this =C1-(C1*Discount). This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.</li><li>Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.</li><li>Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you've got many of these key figures your spreadsheets can become crowded.</li></ul><br />To create a Named Constant See--<a href="http://excel-name-ranges.blogspot.com/2014/03/how-to-setup-named-constant-in-excel.html" target="_blank">How to Setup a Named Constant in Microsoft Excel</a>.<br /><br />

excel microsoft excel Using Named Ranges in Microsoft Excel

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name.You can view the complete tutorial here Ho…

Read more »

Scrape Website Data into Excel using VBA

<div dir="ltr" style="text-align: left;" trbidi="on">I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(dot)renewableuk(dot)com. Please also read the privacy policy of the website before mining data. <br /><input name="IL_IN_TAG" type="hidden" value="1" /> <br /><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="129" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" width="320" /></a><br /><b>Goal:</b><br />Get all data under all column headings which can be found on this <a href="http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm" rel="nofollow" target="_blank">website</a> i.e.<br />Wind Project, Region, ..., Type of Project <br /><br /><b>Requirements:</b><br /><div style="text-align: justify;">You need to add a reference, Microsoft HTML Object Library on your VBA project.</div><br /><b>Usage:</b><br /><div style="text-align: justify;">You can call the <i><b>ProcessWeb()</b></i> sub directly by pressing F5 on the Microsoft Visual Basic Window.</div><div style="text-align: justify;">Or you can add a button on your excel worksheet then assign <b>ProcessWeb()</b> as the macro.</div><br /><b>VBA CODE:</b><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Function ScrapeWebPage(ByVal URL As String)<br /> Dim HTMLDoc As New HTMLDocument<br /> Dim tmpDoc As New HTMLDocument<br /> <br /> Dim i As Integer, row As Integer<br /> Dim WS As Worksheet<br /> Set WS = Sheets("DATA")<br /> <br /> 'create new XMLHTTP Object<br /> Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")<br /> XMLHttpRequest.Open "GET", URL, False<br /> XMLHttpRequest.send<br /> <br /> While XMLHttpRequest.readyState <> 4<br /> DoEvents<br /> Wend<br /><br /> With HTMLDoc.body<br /> 'Set HTML Document<br /> .innerHTML = XMLHttpRequest.responseText<br /> <br /> 'Get only Order List Tag of HTML Document<br /> Set orderedlists = .getElementsByTagName("ol")<br /> <br /> 'Reset the Document to the HTML of the second ordered list element<br /> 'where we only need to extract the data<br /> .innerHTML = orderedlists(1).innerHTML<br /> <br /> 'Now, we'll get the list items<br /> Set ListItems = .getElementsByTagName("li")<br /> <br /> 'Declare data variable for p values<br /> Dim iData As Integer<br /> row = lastRow + 1<br /> <br /> 'Let's process each data of the list items<br /> For Each li In ListItems<br /> 'Start's at 1st column<br /> iData = 1<br /> With tmpDoc.body<br /> 'Set the temp doc<br /> .innerHTML = li.innerHTML<br /> <br /> 'There are about 10 columns, so there are 10 p's<br /> Set ps = .getElementsByTagName("p")<br /> <br /> For Each p In ps<br /> 'Put the value of p to each cells define below<br /> WS.Cells(row, iData).Value = p.innerText<br /> <br /> 'increment it by 1 which starts at column 1<br /> iData = iData + 1<br /> Next<br /> End With<br /> row = row + 1<br /> Next <br /> End With<br />End Function<br /><br />'Get the total number pages we need to scrape<br />Function totalPage() As Integer<br /> Dim HTMLDoc As New HTMLDocument<br /> Dim tmpDoc As New HTMLDocument<br /> Dim html As String<br /> Dim mask As String<br /> Dim URL As String<br /> <br /> URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm"<br /> <br /> Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")<br /> XMLHttpRequest.Open "GET", URL, False<br /> XMLHttpRequest.send<br /> <br /> html = XMLHttpRequest.responseText<br /> <br /> With HTMLDoc.body<br /> .innerHTML = Mid(html, InStr(1, html, "<span chr="" class=" & Chr(34) & " page-dotted="">"), 300)<br /> mask = Mid(.innerHTML, InStr(1, LCase(.innerHTML), "</span>") - 2, 2)<br /> End With<br /> <br /> totalPage = mask<br /> <br />End Function<br /><br />Function lastRow() As Long<br /> lastRow = Range("A65536").End(xlUp).row<br />End Function<br /><br />Sub ProcessWeb()<br /> Dim URL As String<br /> Dim i As Integer<br /> <br /> Range("2:2", Selection.End(xlDown)).ClearContents<br /> Range("A2").Select<br /> <br /> Application.ScreenUpdating = False<br /> Application.Cursor = xlWait<br /> <br /> URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm/page/"<br /> <br /> For i = 1 To totalPage<br /> ScrapeWebPage URL & i<br /> Application.StatusBar = "Please wait while processing page " & i & " of " & totalPage & "..."<br /> Next i<br /> <br /> Application.ScreenUpdating = True<br /> Application.Cursor = xlDefault<br /> Application.StatusBar = ""<br /> <br /> MsgBox "Data Extraction is Done!"<br /> <br />End Sub<br /></pre></div><!--INFOLINKS_ON--> </div>

excel hot microsoft excel Scrape Website Data into Excel using VBA

I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(dot)renewableuk(dot)com. Please also read the privacy policy of the website before mining data. Goal:Get all data und…

Read more »

How to use Do While Loop and a For Loop Counter in VB.Net

<div dir="ltr" style="text-align: left;" trbidi="on">In this tutorial, we'll be working on a real example on how to use <b><span style="color: #c0392b;">Do...While Loop and a For...Loop Counter</span></b> in VB.Net.<br /><br /><b><span style="color: #c0392b;">Example Description:</span></b><br />1. A customer will be asked to enter the prices of 4 items purchased using an input box (create a counter loop).<br />2. A tax rate of 7% will be a constant.<br />3. Subtotal, tax rate, and total will be displayed in labels.<br />4. An accumulator inside the loop will be used to calculate the subtotal.<br />5. A pre-test post-test do while loop shall be used to control the limit of entering price.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbCNCtrt5jKvMDvalzF1gVtusnCQ2ivYUKkSwDMZaSB_McMB8ezM8DpRqRa0HpSuQm0jqPMSv4yzJa_aWuDbKCYutH0LaiC0HAY0Cg9qQ5eVwfWKSn1YlRVxf8yBzLQ2EiZwCqGWNe0wR7/s1600/Do+While+For+Loop+Counter.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbCNCtrt5jKvMDvalzF1gVtusnCQ2ivYUKkSwDMZaSB_McMB8ezM8DpRqRa0HpSuQm0jqPMSv4yzJa_aWuDbKCYutH0LaiC0HAY0Cg9qQ5eVwfWKSn1YlRVxf8yBzLQ2EiZwCqGWNe0wR7/s1600/Do+While+For+Loop+Counter.gif" width="200" /></a></div><br /><b><span style="color: #c0392b;">CODE:</span></b><!--INFOLINKS_OFF--><br /><pre class="brush: vb">Public Class Form1<br /> Private Sub btnEnterPrices_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnterPrices.Click<br /> Dim Price As String<br /> Dim SubTotal As Double, Tax As Double, Total As Double<br /> Dim limit As String, newLimit As String<br /> Dim i As Integer, n As Integer, response As Integer<br /><br /> 'Prompts the user the input the number of prices to be calculated<br /> limit = InputBox("Please enter the number of prices to calculate")<br /><br /> 'Check user input for a valid number<br /> Do While IsNumeric(limit) = False<br /> limit = InputBox("Please enter the number of prices to calculate")<br /> Loop<br /><br /> 'Start the counter up to the limit<br /> For i = 1 To limit<br /> 'Prompts for the price<br /> Price = InputBox("Please enter Price #" & i & ":", "Price # " & i)<br /><br /> 'Check if the Price entered is valid<br /> If IsNumeric(Price) = False Then<br /> 'Display an error message until the user enters a valid price<br /> Do While IsNumeric(Price) = False<br /> MsgBox("Please input another a valid Price", vbExclamation, "Invalid Price")<br /> Price = InputBox("Please enter Price #" & i & ":", "Price # " & i)<br /> Loop<br /> Else<br /> 'Calculate sub-total<br /> SubTotal = SubTotal + Price<br /> End If<br /><br /> If i = limit Then<br /><br /> 'If the limit is reached, <br /> 'prompt the user whether to enter additional prices to calculate<br /> response = MsgBox("Do you want to enter additional Price?", vbYesNo)<br /><br /> 'If the user clicked Yes<br /> If response = vbYes Then<br /><br /> 'Prompt a new limit <br /> newLimit = InputBox("Please enter number of additional Price to Calculate")<br /><br /> 'Validate user input<br /> Do While IsNumeric(newLimit) = False<br /> newLimit = InputBox("Please enter number of additional Price to Calculate")<br /> Loop<br /><br /> 'Start a new counter for the new limit<br /> For n = 1 To newLimit<br /><br /> 'Prompt for the Price<br /> Price = InputBox("Please enter Price #" & i + n & ":", "Price # " & i + n)<br /><br /> 'Validate User Input<br /> If IsNumeric(Price) = False Then<br /><br /> 'Display a message while the price is not a valid number<br /> Do While IsNumeric(Price) = False<br /> MsgBox("Please input another a valid Price", vbExclamation, "Invalid Price")<br /> Price = InputBox("Please enter Price #" & i + n & ":", "Price # " & i + n)<br /> Loop<br /> Else<br /> 'Calculate Subtotal<br /> SubTotal = SubTotal + Price<br /> End If<br /> Next n<br /> End If<br /> End If<br /> Next i<br /><br /> 'Calculate Tax and the Total Price<br /> Tax = Format(SubTotal * 0.07, "###.##")<br /> Total = Format(SubTotal + Tax, "###.##")<br /><br /> 'Display results inside labels<br /> lblSubTotal.Text = "$" & SubTotal<br /> lblTax.Text = "$" & Tax<br /> lblTotal.Text = "$" & Total<br /><br /> End Sub<br /><br /> Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click<br /> Me.Close()<br /> End Sub<br />End Class<br /></pre><!--INFOLINKS_ON--></div>

dotNet VB.NET How to use Do While Loop and a For Loop Counter in VB.Net

In this tutorial, we'll be working on a real example on how to use Do...While Loop and a For...Loop Counter in VB.Net.Example Description:1. A customer will be asked to enter the prices of 4 items purchased using an input box (create a counter loop).…

Read more »

Send Email with Excel VBA via CDO through GMail

<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifrtiqOdy-PFBKU75R4cuFu-nZfvgu3aTnaPkpDVs_qgdlaTEd6QZLGuZ7O5gdW0Hd96oy6zDMlSKcKjuhUNpQ7Y2AYMQ8C4tS9BjesSbOdAD_hITUlexpuhtJWVpNU6hp7gfPlZ391xsR/s1600/Excel+Reports.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="Send Email with Excel VBA" border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifrtiqOdy-PFBKU75R4cuFu-nZfvgu3aTnaPkpDVs_qgdlaTEd6QZLGuZ7O5gdW0Hd96oy6zDMlSKcKjuhUNpQ7Y2AYMQ8C4tS9BjesSbOdAD_hITUlexpuhtJWVpNU6hp7gfPlZ391xsR/s1600/Excel+Reports.png" title="Send Email with Excel VBA" width="200" /></a>If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consuming task!</div><div style="text-align: justify;">What we wanted to do is automate the tasks from within the Excel Workbook you're working with. The <b>SendEmail()</b> Function below will do the task for you.</div><b>Function Definition:</b><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Function SendEmail(ByVal Username As String, _<br />                   ByVal Password As String, _<br />                   ByVal ToAddress As String, _<br />                   ByVal Subject As String, _<br />                   ByVal HTMLMessage As String, _<br />                   ByVal SMTPServer As String, _<br />                   Optional Attachment As Variant = Empty) As Boolean<br /></pre><!--INFOLINKS_ON--><input name="IL_IN_TAG" type="hidden" value="1" /><br /><b>Paramaters: </b><br /><ul><li style="text-align: justify;"><b>Username</b> - is the email address of the sender.</li><li style="text-align: justify;"><b>Password</b> - is the password of the sender.</li><li style="text-align: justify;"><b>ToAddress</b> - is the recipient of email to which the email be sent. Multiple email addresses can be separated with semi-colons.</li><li style="text-align: justify;"><b>Subject</b> - is the subject of the email.</li><li style="text-align: justify;"><b>HTMLMessage </b>- may contain both plain text and html message. </li><li style="text-align: justify;"><b>SMTPServer </b>- is the name of the outgoing email server. If you're connected within a company's intranet you can use your company's outgoing email server. In this tutorial we'll be using gmail's smtp server.</li><li style="text-align: justify;"><b>Attachment </b>- is the file name that will be attached to the message. If you're going to send the workbook that you're working with as an attachment, you can just put <b>ThisWorkbook.FullName</b>.</li></ul><b>Requirement:</b><br />This function requires you to add a reference to<i> Microsoft CDO for Windows 2000</i>. At Microsoft Visual Basic Interface go to <i>Tools>References...</i><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgutoP6cEtsjoswO32DS1cgQON9I0FzjuXskWYyyUsVDxUEGunrfYnvpUbizv_2EMo3j42puGlk8ov_nleer1BVYaZo1peVToOvIT5KwS0Iq-0Gv5vUoKCEnicRx06fyPRkj6z8T36pAHPX/s1600/Microsoft+CDO+for+Windows+2000+Library.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgutoP6cEtsjoswO32DS1cgQON9I0FzjuXskWYyyUsVDxUEGunrfYnvpUbizv_2EMo3j42puGlk8ov_nleer1BVYaZo1peVToOvIT5KwS0Iq-0Gv5vUoKCEnicRx06fyPRkj6z8T36pAHPX/s1600/Microsoft+CDO+for+Windows+2000+Library.png" width="400" /></a></div><br /><b>CONFIG SETUP:</b><br />You may also create another sheet for the configuration setup and assign names to ranges or fields.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHOEgsA3epgUjqm_ijCvvi4WaIKM9xu89uR2ykg7xenoJyjhltvurQDPryzGeCKp7hifOkFR0DHMl1poWG5VHsI9iDiljnsoaOF40pz8gfVVItSE49i2QZWo1CoxDFTO3O2RRQkePLDnAu/s1600/Send+Email+with+Excel+VBA+Configuration+Setup.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHOEgsA3epgUjqm_ijCvvi4WaIKM9xu89uR2ykg7xenoJyjhltvurQDPryzGeCKp7hifOkFR0DHMl1poWG5VHsI9iDiljnsoaOF40pz8gfVVItSE49i2QZWo1CoxDFTO3O2RRQkePLDnAu/s1600/Send+Email+with+Excel+VBA+Configuration+Setup.png" width="400" /></a></div><br /><br /><b>USAGE:</b><br />You can call the function via a click of a button or when a target is changed on a worksheet.<br /><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Sub Send()<br /> Dim Ws As Worksheet<br /> Dim Attachment As String<br /> <br /> Set Ws = ActiveSheet<br /> <br /> With Ws<br /><br /> If Trim(.Range("ATTACHMENT")) = "" Then<br /> ThisWorkbook.Save<br /> ThisWorkbook.ChangeFileAccess xlReadOnly<br /> Attachment = ThisWorkbook.FullName<br /> ThisWorkbook.ChangeFileAccess xlReadWrite<br /> Else<br /> Attachment = .Range("ATTACHMENT")<br /> End If<br /><br /> 'CHECK WHETHER THE FUNCTION RETURNS TRUE OR FALSE<br /> If SendEmail(.Range("SENDER"), .Range("PASS"), .Range("RECIPIENT"), _<br /> .Range("SUBJECT"), .Range("MESSAGE"), .Range("SMTP"), Attachment) = True Then<br /> MsgBox "Email was successfully sent to " & .Range("RECIPIENT") & ".", vbInformation, "Sending Successful"<br /> Else<br /> MsgBox "A problem has occurred while trying to send email.", vbCritical, "Sending Failed"<br /> End If<br /><br /> End With<br /><br />End Sub<br /></pre><input name="IL_IN_TAG" type="hidden" value="1" /> <br /><b>FULL VBA CODE:</b><br /><br /><pre class="brush: vb">Function SendEmail(ByVal Username As String, _<br /> ByVal Password As String, _<br /> ByVal ToAddress As String, _<br /> ByVal Subject As String, _<br /> ByVal HTMLMessage As String, _<br /> ByVal SMTPServer As String, _<br /> Optional Attachment As Variant = Empty) As Boolean<br /><br /> Dim Mail As New Message<br /> Dim Cfg As Configuration<br /> <br /> 'CHECK FOR EMPTY AND INVALID PARAMETER VALUES<br /> If Trim(Username) = "" Or _<br /> InStr(1, Trim(Username), "@") = 0 Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(Password) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(Subject) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(SMTPServer) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> <br /> On Error Resume Next<br /> Set Cfg = Mail.Configuration<br /> <br /> 'SETUP MAIL CONFIGURATION FIELDS<br /> Cfg(cdoSendUsingMethod) = cdoSendUsingPort<br /> Cfg(cdoSMTPServer) = SMTPServer<br /> Cfg(cdoSMTPServerPort) = 25<br /> Cfg(cdoSMTPAuthenticate) = cdoBasic<br /> Cfg(cdoSMTPUseSSL) = True<br /> Cfg(cdoSendUserName) = Username<br /> Cfg(cdoSendPassword) = Password<br /> Cfg.Fields.Update<br /> <br /> If err.Number <> 0 Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> err.Clear<br /> <br /> On Error GoTo 0<br /> With Mail<br /> .From = Username<br /> .To = ToAddress<br /> .Subject = Subject<br /> .HTMLBody = HTMLMessage<br /><br /> If Attachment <> "" Then<br /> .AddAttachment Attachment<br /> End If<br /> <br /> On Error Resume Next<br /> err.Clear<br /><br /> 'SEND EMAIL<br /> .Send<br /> End With<br /> If err.Number = 0 Then<br /> SendEmail = True<br /> Else<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br />End Function<br /></pre><!--INFOLINKS_ON--> <b><br /></b><b>RESULTS:</b><br />Below are the results after running the above code snippet.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQuqf1XwdBlFv6zOe_1IGCKo2d-67LWQS3bC2HHfw7-_lR38YFiwWCZgSEl8VlaIhGYQFXM3r8i0HEgou-eeSaffL7OFcCXHfQttQH7R9rg6um3fQ7PXjuzsb_Uc4sPvn0YY0_pllbz_G4/s1600/Sending+Email+Successful.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="182" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQuqf1XwdBlFv6zOe_1IGCKo2d-67LWQS3bC2HHfw7-_lR38YFiwWCZgSEl8VlaIhGYQFXM3r8i0HEgou-eeSaffL7OFcCXHfQttQH7R9rg6um3fQ7PXjuzsb_Uc4sPvn0YY0_pllbz_G4/s1600/Sending+Email+Successful.png" width="400" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhASR31__rfAPBn3se3GkJ3bd2tVLynkSpP-f0m0Lv7ijxTfJfXS-g3CShyf-C-YMl3t4XwIRQ3jG4xQ59TLI-9-rFZ6cAVcMNls_fIGpEcJyauttyAWr8oDzNUhRvkUd_x2U6ADB5ZeTJd/s1600/Sending+Email+Successful+Gmail.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhASR31__rfAPBn3se3GkJ3bd2tVLynkSpP-f0m0Lv7ijxTfJfXS-g3CShyf-C-YMl3t4XwIRQ3jG4xQ59TLI-9-rFZ6cAVcMNls_fIGpEcJyauttyAWr8oDzNUhRvkUd_x2U6ADB5ZeTJd/s1600/Sending+Email+Successful+Gmail.png" width="400" /></a></div><br /></div>

excel microsoft excel VBA Send Email with Excel VBA via CDO through GMail

If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consumi…

Read more »

The Commonly Used Excel Functions

<div dir="ltr" style="text-align: left;" trbidi="on">Some Excel functions may apply to specific subject areas, while others are general and can be applied to all needs. The following shows a list of Excel Functions commonly used by everyone.<br /><br /><table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 555px;"><colgroup><col style="mso-width-alt: 3620; mso-width-source: userset; width: 74pt;" width="99"></col><col style="mso-width-alt: 16676; mso-width-source: userset; width: 342pt;" width="456"></col></colgroup><tbody></tbody></table><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWHa-4RMnzFTTWFzTLegj9ki52xZHHzuy0R1Joj-_PB_LWBfqSkxS0lNalNTVmIx-pBO8wflMzBpX1GZnVNvCm540SR7unOtrBG91tb2qW0BpyvsIsPvw2poUI-IBowzENXItibsN0dyig/s1600/Most+Commonly+Used+Excel+Functions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWHa-4RMnzFTTWFzTLegj9ki52xZHHzuy0R1Joj-_PB_LWBfqSkxS0lNalNTVmIx-pBO8wflMzBpX1GZnVNvCm540SR7unOtrBG91tb2qW0BpyvsIsPvw2poUI-IBowzENXItibsN0dyig/s1600/Most+Commonly+Used+Excel+Functions.png" width="400" /></a></div></div>

excel microsoft excel The Commonly Used Excel Functions

Some Excel functions may apply to specific subject areas, while others are general and can be applied to all needs. The following shows a list of Excel Functions commonly used by everyone.…

Read more »