Thank you for Visiting my Blog

Sunday, 26 March 2017

Run Excel Macro using VBScript Script File



Save below content in a notepad with ".VBS" extension .Once it successfully save click on the file icon to run it .














 Set objExcel = CreateObject("Excel.Application")
' excel complete path including macro name  --------------'

objExcel.Application.Run "'C:\Users\Jaya\Desktop\Run_Macro.xlsm'!runmacromodule.runmacro"

objExcel.DisplayAlerts = False
objExcel.Application.Save
objExcel.Application.Quit
Set objExcel = Nothing


Customization:

1. The objExcel.Application.Run would check for Excel sheet path, Module name and macro name which need to run...
 
objExcel.Application.Run "'Path\WorkbookName.xlsm'!Module.Macro_Name"


2. objExcel.Application.Save , will ensure that work book should be saved after successfully execution  and before quitting the program objExcel.Application.Quit.













 

How to connect to OUTLOOK mailbox using Macros




Sub Mail_Connection()

' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.


    Dim OutApp As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set OutApp = CreateObject("Outlook.Application")

    On Error Resume Next
    Dim objSentFolder As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim sFilter As String
    Dim mySubject As String
    Dim myItem As Outlook.MailItem
    Dim Mail_count As Integer
   
    Set objSentFolder = _
    OutApp.GetNamespace("MAPI").GetDefaultFolder(6).Folders("temp")
 
    Set myItems = objSentFolder.Items
    mySubject = "Good Morning"
   
    sFilter = "[Subject] = '" & mySubject & "'"
    myItem = myItems.Find(sFilter)
    Mail_count = myItems.Count

   
    For i = 1 To Mail_count
       If mySubject = myItems(i).Subject Then
            ThisWorkbook.Worksheets("temp").Range("A" & i).Value = myItems(i).Body
       End If
   
    Next i
   
  
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 

Tuesday, 21 March 2017

How to break one cell values into multiple rows in Macro


How to break one cell values into multiple rows in Macro


Workbook Name: Celltorowconverter.xlsm
Input worksheet Name:Temp
Outputworksheet Name:Result















Below is the code I written to extract the value from Cell to rows:

Sub celltorow()

'---------- declare Variables

Dim Textstring As String
Dim splitVals() As String
Dim totalVals As Integer
Dim Startvals As Integer

Dim i As Integer

'----get the value in Variable

Textstring = ThisWorkbook.Worksheets("Temp").Cells(2, 1).Value

'-----Split the Variable value from new line and stored in array

splitVals() = Split(Textstring, vbNewLine)

'---get the starting and values Array

Startvals = LBound(splitVals())

totalVals = UBound(splitVals())


For i = Startvals + 1 To totalVals
ThisWorkbook.Worksheets("Result").Activate

ThisWorkbook.Worksheets("Result").Range("A" & i).Value = splitVals(i)

Next i

End Sub

Output would be like below:




Sunday, 19 March 2017

Move mail from one folder to another using macros or VBA

Hello Guys!!!


To move any mail item from one folder to another  using Macros or VBA you need to connect to your outlook mail .below I mentioned the step how I connected to mine  .........

Start sub function in your excel sheet inside Visual basics.

Sub Mail_Sheet_Outlook_Body()

' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    ' You can also use a sheet name here.
    'Set rng = Sheets("YourSheet").UsedRange
    Set OutApp = CreateObject("Outlook.Application")

    On Error Resume Next
    Dim objSentFolder As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim sFilter As String
    Dim mySubject As String
    Dim myItem As Outlook.MailItem
    Dim myNewItem As Outlook.MailItem
    Dim Str As String
    Dim Mail_count As Integer
    Dim moveToFolder As Outlook.MAPIFolder
  
' set the input folder where actually mail exist  . in below example my mail exist in "temp" folder which is under inbox .

    Set objSentFolder = _
    OutApp.GetNamespace("MAPI").GetDefaultFolder(6).Folders("temp")
 
    Set myItems = objSentFolder.Items
   
    ' Setting the another folder where item should be moved. below example I am moving my mail from temp folder to Staging folder . both are under inbox.

    Set moveToFolder = OutApp.GetNamespace("MAPI").GetDefaultFolder(6).Folders("Staging")
     
'define the subject if you want any specific mail to move. below I am moving all the mail which is having subject learning
   
mySubject = "Learning"
   
   
    sFilter = "[Subject] = '" & mySubject & "'"
    myItem = myItems.Find(sFilter)
    Mail_count = myItems.Count
   
'below for loop will run to into temp folder till it find all the mail which is started from "Learning"

    For i = 1 To Mail_count
      If mySubject = myItems(i).mySubject Then
    
          
         myItems(i).Move moveToFolder

      End If
   
    Next i
   
  
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub



This is for today guys if you have any issue to understand this comment and let me know till than Happy learning...................