Thursday, October 31, 2013

Outlook mail draft & coverting data into html format

Below is the code to create draft emails and paste range of data from Excel into the body of the email.

'Mail Draft coding
Private Function Mail_Range_Outlook_Body(DRng As String)
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

'Application.EnableEvents = False
Set rng = Nothing
Set rng = SW_Rpts.Range(DRng)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
    .To = SW_Rpts.Range("AG1").Value
    .CC = SW_Rpts.Range("AG2").Value
    .BCC = ""
    .Subject = SW_Rpts.Range("AG3").Value
    If SW_Rpts.Range("AG4").Value <> "" Then .Attachments.Add SW_Rpts.Range("AG4").Value
    .HTMLBody = RangetoHTML(rng)
    .Display   '.Send
    .Save
    .Close olPromtForSave
End With
'Application.EnableEvents = True

Set OutMail = Nothing
Set OutApp = Nothing
End Function

'Coverting data into html format
Public Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "\Surveys_Weekly_Temp.htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).Range("A1:J" & (Range("A100").End(xlUp).Row + 3)).Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

TempWB.Close SaveChanges:=False
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Saturday, October 26, 2013

Microsoft Excel

Microsoft Excel is developed by Microsoft and Excel is a part of MS-Office package. We all use Excel in our day to day work. It is used for various purposes like storing employee details, graphs, pivots to visually present the data, etc.

For example, below image contains sales data by store, region and category. It is also presented graphically. Graphical presentation of data in Excel makes it more easy to understand by business.

If you click on fx button you can see the list of functions Excel has. You can also create your own function using VBA macro.

How To Shutdown Your Computer Remotely Via Email For Microsoft OS

You might have come across with a situation when you have kept your system to perform some tasks while you are travelling. At the same time you want to make sure the system shuts down after the job is performed. You can do this by triggering an event from Outlook.

Refer to the below link to understand how you can shutdown your computer remotely via email:

Thursday, October 17, 2013

List out named ranges with VBA

You may have a list of named ranges in Excel and you want to list down all in a sheet.
Use the below code to get the list of all the available named ranges in the workbook.

Sub ListNames()
Dim wks As Worksheet
Set wks = Worksheets.Add
wks.Range("A1").ListNames
End Sub

Tuesday, October 15, 2013

Keyboard shortcuts

Some useful keyboard shortcuts:

Shift+Ctrl+1, 2, 3, 4 or 5" = Numeric, Time, Date, $ and %.

Run Macro if cell value changes

There are certain situations when one wants to execute the code based on a particular cell. If the value of that particular cell changes the code should execute,here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$BA$27" Then
        'Do your stuff here
    End If
End Sub