VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Another powerful feature of Excel VBA is to interact with Other Applications. We can deal with Other MS Office Applications (Word, PowerPoint,Access). We can also deal with Other Applications like Internet Explorer, SAS, VBScript from Excel. I will show some examples on interacting with different Application from Excel.

Interact with PowerPoint from Excel VBA

The following code will show you how to deal and interact with PowerPoint. We can create PowerPoint presentation from Excel or modify the existing presentation using Excel VBA.

Add Reference:Microsoft PowerPoint Object Library

Sub sbPowePoint_SendDataFromExcelToPPT()

    Dim oPPT As PowerPoint.Application
    Dim oPPres As PowerPoint.Presentation
    Dim oPSlide As PowerPoint.Slide
    Dim sText As String
'Open PowerPoint
    Set oPPT = New PowerPoint.Application
    Set oPPres = oPPT.Presentations.Add
    oPPT.Visible = True
'Add a Slide
    Set oPSlide = oPPres.Slides.Add(1, ppLayoutTitleOnly)

'Copy a range as a picture and align it
    ActiveSheet.Range("A1:B10").CopyPicture Appearance:=xlScreen, Format:=xlPicture
    oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    oPPT.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
'Add the title text
    sText = "My Header"
    oPSlide.Shapes.Title.TextFrame.TextRange.Text = sText


'Release Objects
    Set oPSlide = Nothing
    Set oPPres = Nothing
    Set oPPT = Nothing

End Sub

Dealing with MS Word From Excel VBA

The following code will show you how to deal and interact with Word. We can create Word document from scratch or modify the existing document using Excel VBA.

Sub sbWord_FormatingWordDoc()

Dim oWApp As Word.Application
Dim oWDoc As Word.Document
Dim sText As String
Dim iCntr As Long

Set oWApp = New Word.Application
Set oWDoc = oWApp.Documents.Add() '("") 'You can specify your template here

'Adding new Paragraph

Dim para As Paragraph
Set para = oWDoc.Paragraphs.Add

para.Range.Text = "Paragraph 1 - My Heading"
para.Format.Alignment = wdAlignParagraphCenter
para.Range.Font.Size = 18
para.Range.Font.Name = "Cambria"

For i = 0 To 2
    Set para = oWDoc.Paragraphs.Add

Set para = oWDoc.Paragraphs.Add
With para
   .Range.Text = "Paragraph 2 - Some Text for the next Paragraph"
    .Alignment = wdAlignParagraphLeft
   .Range.Font.Size = 14
   .Range.Font.Bold = True
End With


Set para = oWDoc.Paragraphs.Add
With para
   .Range.Text = "Paragraph 3 - This is another Paragraph, you can create number of paragraphs like this and format it"
    .Alignment = wdAlignParagraphLeft
   .Range.Font.Size = 12
   .Range.Font.Bold = False
End With

oWApp.Visible = True

End Sub

Interact with MS Access from Excel VBA

The following code will show you how to deal and interact with Access.

Add Reference: Microsoft Access Object Library

Sub sbAccess_OpenAForm()

'Declaring Access Application
Dim oAApp As Access.Application

'Connecting Access Data base
Set oAApp = New Access.Application
oAApp.OpenCurrentDatabase ("C:ExampleDatabase.accdb")

'Opening a From
    With oAApp
        .DoCmd.OpenForm "MyForm", acNormal
        .Visible = True
    End With

End Sub

Interact with Outlook from Excel VBA

Sub sbOutlook_SendAMail()

Dim oOApp As Object
Dim oMail As Object

Set oOApp = CreateObject("Outlook.Application")
Set oMail = oOApp.CreateItem(0)

 On Error Resume Next
' Change the mail address and subject in the macro before you run it.
 With oMail
     .To = ""
     .CC = ""
     .BCC = ""
     .Subject = "Write Your Subject Here"
     .Body = "Hi, This is example Body Text."

     '.Attachments.Add ("C:TempExampleFile.xls") '=> To add any Attcahment
     .Display '=> It will display the message
     '.Send '=> It will send the mail
 End With
 On Error GoTo 0

 Set oMail = Nothing
 Set oOApp = Nothing

End Sub

Interact with MS Word from Excel VBA -Another Example

Add Reference: Microsoft Word Object Library

Sub sbWord_ExcelToWord()

Dim oWApp As Word.Application
Dim oWDoc As Word.Document
Dim sText As String
Dim iCntr As Long

set oWApp = New Word.Application
Set oWDoc = oWApp.Documents.Add() '("") 'You can specify your template here

For iCntr = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    sText = Cells(iCntr, 1)
    sText = sText & " " & Cells(iCntr, 2)
    sText = sText & " " & Cells(iCntr, 3)
    sText = sText & " " & Cells(iCntr, 4)
    oWDoc.Content.InsertAfter (sText)
Next iCntr

oWApp.Visible = True

' Releasing objects
Set oWDoc = Nothing
Set oWApp = Nothing

End Sub

Dealing with Internet Explorer

The following code will show you how to deal and interact with Internet Explorer.

Sub sbIE_OpenASite()

Dim IE As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' Send the form data To URL As POST binary request
IE.Navigate ""

' Wait while loading...
Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)

IE.Visible = True

Set IE = Nothing

End Sub

Dealing with Other Applications from Excel VBA – Calculator

The following code will show you how to deal and interact with Calculator.

Sub sbAnyApplication_OpenCalculator()

Dim sProg As String
Dim tID As Double
On Error Resume Next
sProg = "Calc.exe"
tID = Shell(sProg)
If Err <> 0 Then
    MsgBox "Can't Start Calculator"
End If

End Sub

Run VBScript from Excel VBA

Sub sbVBScript_RunVBS()

    Dim SFilename As String
    SFilename = "C:TempTest.vbs" 'Change the file path

    ' Run VBScript file
    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run """" & SFilename & """"
End Sub

VBA to Attach Send An Excel Chart to Outlook Email

Sub emailingProgram()
    Dim olapp As Outlook.Application
    Dim objmail As Outlook.mailitem
    Dim pos As Integer
    Set olapp = Outlook.Application
    For Each xcell In Sheets("Sheet1").Range(Range("RangetoCopy"), _
        msgText = Range("Msg")
        ActiveCell.Offset(0, 1).Select
        'If you think that the email ID is in the pattern use this if block
        'The code will go into the else statement if the First Name is not mentioned
        If Selection.Value = "" Then
            pos = InStr(1, xcell.Value, ".")
            Fname = Mid$(xcell.Value, 1, InStr(1, xcell.Value, ".") - 1)
            'If you have mentioned the first names in the First Name column this part will read it directly
            Fname = Selection.Value
        End If
        'For each of the cells present in the To List we create a MailItem and send it
        Set objmail = olapp.CreateItem(olMailItem)
        objmail.BodyFormat = olFormatRichText
        'Setting the subject
        objmail.Subject = "Example Subject"
        'Uncomment the following line of code in case you want to send a plain message
        'objmail.Body = "Hi " + UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) + "," + Chr(13) + Chr(10) + msgText
        'For using an image in your mail or an HTML body for styling
        objmail.HTMLBody = "<p><font size='6' face='arial' color='red'><i>Dear " & UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) & "<br></font></p><br><p align='CENTER'><font size='5' face='COMIC SANS' color='RED'>Wishing you a Wonderful Birthday</p><br><br></font><p align='CENTER'><a href=''><img src='' width=450 height=412 border=0></a></a><br><br><br><p align='left'>Thanks & Regards <br><br/> _<p><p align='left'><br>Anshuman Pandey<br></p>"
        objmail.To =
        Set objmail = Nothing
    Next xcell
End Sub

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: April 15, 2013

One Comment

  1. Marvin Crockett June 20, 2019 at 1:11 AM

    I am trying to create a GUI with VBA that will populate information to both an Excel spreadsheet and a Word table document. Can you provide any guidance? Please feel free to contact me directly from the provided information. Thanks for your time.

Leave A Comment