E-Mail Spell Checking Error

philgarside

Board Regular
Joined
Mar 31, 2005
Messages
86
Hi All,

I’ve built a user form that uses this code to send the information they have entered to myself.

Code:
Sub email()

'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "dd-mm-yy h-mm-ss")
    Application.ScreenUpdating = False
    Sheets("Submit Sheet").Copy
    Set wb = ActiveWorkbook
    With wb
        .SaveAs "Part of " & ThisWorkbook.Name _
              & " " & strdate & ".xls"
        Set OutApp = CreateObject("Outlook.Application")
        
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = "Phil.Garside@service.britgas.co.uk"
            .CC = ""
            .BCC = ""
            .Subject = "CRA Compliance Check Form SCMO"
            .Body = "Please find attached the submitted case's for the SCMO."
            .Attachments.Add wb.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Display
            Application.Wait (Now + TimeValue("0:00:02"))
            Application.SendKeys "%S"
            Application.SendKeys "%S"
            Application.Wait (Now + TimeValue("0:00:02"))
        End With
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing

Unload UserForm1
UserForm1.Hide

Sheets("Submit Sheet").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
Sheets("Cover").Select

End Sub

The problem I have is that I don’t want the user to have to click anything. E.g. send, and this code work fine for that. The issue I have is that some of my users have auto spell checker set up in outlook so abbreviations pop up as incorrect spelling. This means that they have to spell check the e-mail before it sends. Dose anyone know of any code that will ignore any spell checking suggestions?

Thanks for your help,

Phil
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
Hi Phil,

I did some searching and I believe that disabling the spell checker in a VBA routine like this is not possible. There is no spell checking object or property in the Outlook object model, it appears to be controlled by a registry setting. Even if your VBA modified the registry setting it might require an exit and restart of Outlook for the change to take effect.

However, there is possibly a good workaround. There is a pretty comprehensive set of examples for sending email from Excel on this site:

http://www.rondebruin.nl/sendmail.htm

Check out the CDO examples. This will bypass Outlook entirely, and you should still be able to accomplish what you need.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,129
Members
412,305
Latest member
Mozz
Top