Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 11th, 2011, 05:05 AM
Friend of Wrox
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Outlook/excel spellcheck


I have question regarding Outlook VBA, i created a macro in Excel which looked at a OUtlook email and checked everything including the address, subject and attachments, and it also peformed a spell check on the body content by copying and pasting it into excel and then spellchecking it.

However i want to move this macro to however so far i have managed to do everything except peform a spell check on the body content. So far i ahve the code below, which when started will check for a to email address and cancel if there isn't one, then if there is a to address, it will do the same for the subject. It will then check the body content for the word attach and if there is but no attachments it will check this with the user. However when it gets to the line marked in red in the spell check part it fails.

Sub EmailCheck()

Dim objmail As Object
Dim ReceieverCheck As String
Dim SubjectCheck As String
Dim SpellCheck As String
Dim SpellCheck1 As String
Dim AttachCheck As String
Dim AttachCount As Integer
Dim strThismsg As String

Set objmail = ActiveInspector.CurrentItem

If objmail.To = "" Then
    MsgBox "There is no address in the To box. Please add one and re-run check", vbOKOnly, "No Subject"
    Exit Sub
    ReceieverCheck = MsgBox("This Message is being sent to: '" & objmail.To & objmail.CC & "' '" & "' Is this correct", vbYesNo, "Reciever Check")
        If ReceieverCheck = vbNo Then
            Exit Sub
        End If
End If

If objmail.Subject = "" Then
    MsgBox "There is no subject in Subject box. Please add one and re-run check", vbOKOnly, "No Subject"
    Exit Sub
    SubjectCheck = MsgBox("This Message has a subject of: '" & objmail.Subject & "' Is this correct", vbYesNo, "Subject Check")
        If SubjectCheck = vbNo Then
            Exit Sub
        End If
End If

AttachCount = objmail.Attachments.Count
strThismsg = objmail.Body

If InStr(LCase(strThismsg), "attach") > 0 Then
    If AttachCount = 0 Then
        AttachCheck = MsgBox("Your email mentions attachments however there are no documents attached. Is this correct?", vbYesNo, "Attachment Check")
            If AttachCheck = vbNo Then
                Exit Sub
        End If
        AttachCheck = MsgBox("There are " & AttachCount & " attachments on this email. Is this correct?", vbYesNo, "Attachment Check")
            If AttachCheck = vbNo Then
                Exit Sub
            End If
    End If
End If

SpellCheck = MsgBox("Has this message been spell checked?", vbYesNo, "Spelling Check")
    If SpellCheck = vbNo Then
        SpellCheck1 = MsgBox("Select Yes to perform a spell check or No to continue", vbYesNo, "Spellcheck")
            If SpellCheck1 = vbYes Then
            End If
      End If

MsgBox "Email Check Complete. Select ok and Send the message", vbOKOnly, "Check Complete"
End Sub
What i want is for it to ask the user if the body has been spell checked and if not to ask the user to select yes to spellcheck or no to continue. When the user selects yes i wanted it to spellcheck the body of the email.

The spellcheck code i used worked in excel when i selected the cell and did selection.spellcheck.

Can anyone tell me what is wrong with the code?


Old October 11th, 2011, 07:29 AM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

it sounds like you have an outlook vba question, so this would be the wrong forum. Also, you should post the error message.

looking through the Outlook object browser, it appears that outlook vba doesn't expose a spellcheck object, so you'll have to open an instance of Excel (or Word) and use the spell check object in that application

hope that helps

Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook VBA Beginner Q, Outlook get email address from Excel file summerlight BOOK: Professional Outlook 2007 Programming ISBN: 978-0-470-04994-5 0 December 28th, 2010 06:22 AM
Mail Excel sheet in outlook with original name. Europom Excel VBA 1 April 26th, 2009 10:11 PM
VBA in excel and outlook dlindamood2000 Excel VBA 19 December 7th, 2006 10:16 AM
Export Excel data to an Outlook email justin353715 Excel VBA 0 October 17th, 2006 12:37 PM
VBA Excel, outlook createobject Willie Johnson Jr Excel VBA 0 March 10th, 2006 05:11 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.