+ Reply to Thread
Results 1 to 3 of 3

Convert this word VBA to excel

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Convert this word VBA to excel

    How would I convert this Word VBA to Excel?


    Sub SendDocAsMail()
    
    Dim oOutlookApp As Outlook.Application
    Dim oItem As Outlook.MailItem
    
    On Error Resume Next
    
    'Start Outlook if it isn't running
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        Set oOutlookApp = CreateObject("Outlook.Application")
    End If
    
    'Create a new message
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    
    'Allow the user to write a short intro and put it at the top of the body
    Dim msgIntro As String
    msgIntro = InputBox("Enter any words of wisdom " & _
                "you would like to add." & vbCrLf & vbCrLf & _
                "Press Cancel to create the mail without intro and " & _
                "signature.", "Intro")
    
    'Copy the open document
    Selection.WholeStory
    Selection.Copy
    Selection.End = True
    
    'Set the WordEditor
    Dim objInsp As Outlook.Inspector
    Dim wdEditor As Word.Document
    Set objInsp = oItem.GetInspector
    Set wdEditor = objInsp.WordEditor
    
    'Write the intro if specified
    Dim i As Integer
    If msgIntro = IsNothing Then
        i = 1
        'Comment the next line to leave your default signature below the document
        wdEditor.Content.Delete
    Else
        'Write the intro above the signature
        wdEditor.Characters(1).InsertBefore (msgIntro)
        i = wdEditor.Characters.Count
        wdEditor.Characters(i).InlineShapes.AddHorizontalLineStandard
        wdEditor.Characters(i + 1).InsertParagraph
        i = i + 2
    End If
    
    'Place the current document under the intro and signature
    wdEditor.Characters(i).PasteAndFormat (wdFormatOriginalFormatting)
    
    'Display the message
    oItem.Display
    
    'Clean up
    Set oItem = Nothing
    Set oOutlookApp = Nothing
    Set objInsp = Nothing
    Set wdEditor = Nothing
    
    End Sub

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Convert this word VBA to excel

    Hi, bdouglas1011,

    without knowing how your workbook or range looks like and what you want it to look in Outlook maybe have a look at Mail from Excel with Outlook (Windows) where you should find a fitting code for your need or attach a sample workbook with situation as is and as wanted.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    647

    Re: Convert this word VBA to excel

    I have attached the sheet. What I would like is when the user clicks on the macro button for it to send to email I would like the macro:
    1. The macro needs to look and see if outlook is running and if not go ahead and start it first
    2. Copy the spreadsheet and place it directly into outlook. Hopefully the Logo Picture Comes with it.
    3. I would also like it to save a PDF of the Morning report into a File folder called Morning Reports on the desktop.
    4. I would like it to attach the workbook to the email
    5. I would like it to place the default email signature below the body of email.
    6. I would also like once you click on the macro a pop up question would show "If you would like to enter a comment before your email Choose YES or Choose NO to go directly to email."

    Thanks for your help
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert Word to Excel based upon Word Table of Contents
    By jcappuccino7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-06-2013, 01:25 PM
  2. convert excel to word
    By Joe Black in forum Excel General
    Replies: 2
    Last Post: 11-23-2005, 08:55 PM
  3. Convert Word to excel
    By sostabarak in forum Excel General
    Replies: 1
    Last Post: 11-01-2005, 10:05 AM
  4. convert a Word doc into an Excel doc.
    By Tedb in forum Excel General
    Replies: 1
    Last Post: 07-28-2005, 03:05 PM
  5. [SOLVED] convert word doc to excel
    By cyndimac in forum Excel General
    Replies: 3
    Last Post: 03-23-2005, 11:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1