Can I send an email from excel and turn off the spell checker? 1

[ssmgr]

I'd like to be able to send an email from Excel, but don't want the spell checker to fire up. Is there a way to turn spell checking off?

 

[ClulessChris]

Set a reference to Outlook.

But this hit the Outlook security (user will be given an alert that a program is trying to send mail, and be given the chance to cancel it).

Sub SendMail()
    Dim oOl         As Outlook.Application
    Dim oItem       As MailItem
    
    On Error GoTo SendMail_Err
    
    Set oOl = New Outlook.Application
    Set oItem = oOl.CreateItem(olMailItem)
    With oItem
        .To = "6021134"
        .Body = "my big chicin awli"
        .Send
    End With

Tidy_Up:
    Set oItem = Nothing
    Set oOl = Nothing
    Exit Sub
    
SendMail_Err:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    
End Sub

Everybody body is somebodys Nutter.

 

[Andrzejek]

You may also check this:

http://www.tek-tips.com/viewthread.cfm?qid=1394046

Have fun.

---- Andy

 

[ssmgr]

ClulessChris & Andrzejek,
Thanks for the advice. I already had the code to send an email from Excel, and the body of that email contains the contents of various Excel cells. However, I am coming unstuck if the contents of those Excel cells contain words that aren't in the dictionary (such as surnames etc). When I send the email, a dialogue box displays to advise that a misspelt word has been detected. This box also suggests various replacement words. I want to prevent this dialogue box from displaying even if the body of the email contains words that aren't in the dictionary. In online Outlook under Tools->Options there's a spelling tab, and one of the check boxes is "Always check spelling before sending". What I want to do is effectively turn that off using VBA, but only for the email I'm sending. Is it possible? Any advice appreciated.

 

[WinblowsME]

ssmgr, post your code. I've enabled the Outlook spell-checker and used Chris' code and the spell-checker never came up. What version of Excel/Outlook are you using?

 

[ssmgr]

Here 'tis:

Private Sub sendemail(regno As String, unitname As String, surname1 As String, initial1 As String, course1 As String, location1 As String, date1 As String, traincost As Currency, travelcost As Currency, rowno As Long)
                                             

Dim objol As New Outlook.Application
Dim objmail As MailItem

On Error GoTo SendMail_Err

Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)

With objmail
  .To = "someone@someplace.net.au"                    'enter in here the email address
  .Subject = "Funds Register Update"
  .Body = Application.UserName & " has updated row " & rowno & " as follows:" & vbCrLf & _
          "Registration Number: " & regno & vbCrLf & _
          "Business Unit: " & unitname & vbCrLf & _
          "Surname: " & surname1 & vbCrLf & _
          "Initials: " & initial1 & vbCrLf & _
          "Course Details: " & course1 & vbCrLf & _
          "Location: " & location1 & vbCrLf & _
          "Dates: " & date1 & vbCrLf & _
          "Training Cost: " & traincost & vbCrLf & _
          "Travel Cost: " & travelcost & vbCrLf
  .DeleteAfterSubmit = True
  .NoAging = True
      '  .Attachments.Add PathName
  .Display
End With

Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True    'send the email without security checking
Exit Sub
                       
SendMail_Err:
  DoEvents
  
End Sub

I modified it to look more like Chris's by incuding an on error goto statement, but I still get the spell checker coming up if the surname passed in is not in the dictionary.

 

[ClulessChris]

ssmgr,

I notice that your routine is using the .display method and not the .send method
I assume the intention is that the user sees the the mail and clicks 'send' themselves?
If this is the intention then I'm not sure that the spell checker can be turned off on an item to item basis.

But then I may be wrong.

Everybody body is somebodys Nutter.

 

[WinblowsME]

ssmgr, since you don't mind using SendKeys, try

   SendKeys "%{s}", True
   SendKeys "{ESC}", True
   SendKeys "%{y}", True

 

[ssmgr]

Cluless Chris,
I'm using .Display and Sendkeys instead of .Send to avoid getting the XP security alert dialogue box that warns when a program is attempting to send an email. .Display and Sendkeys works fine if there are no spelling errors - the user that's updating Excel will generate and send an email without even knowing it. However, if the user enters a word in Excel that isn't in the dictionary, Outlook runs the spell checker over it before sending, and the user will get a prompt to correct the error, ignore it, or cancel the email transmission. My Outlook is set up to use Word as it's editor, so I assume that Outlook invokes Word's spell checker.

WinblowsMe, I tried your idea and it seems to work. I'd prefer to be able to turn off spell checking when I send and turn it back on again afterwards, but cancelling the spelling dialogue box is a good work around. The only downside is that if there are no spelling errors the Sendkeys commands are unnecessary and result in a beep of protest, but I can live with that as a stopgap. Thank you.

I'd still love to hear from anyone who knows how to stop Outlook from calling up the spell checker when it's sending an email.

 

[WinblowsME]

You should consider the solutions that were discussed in Andrzejek's link.

[COLOR=red]
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long[/color]

Private Sub sendemail(regno As String, unitname As String, surname1 As String, initial1 As String, course1 As String, location1 As String, date1 As String, traincost As Currency, travelcost As Currency, rowno As Long)
   Dim objol As New Outlook.Application
   Dim objmail As MailItem
   Dim win_wnd As Long
   
   On Error GoTo SendMail_Err
   
   Set objol = New Outlook.Application
   Set objmail = objol.CreateItem(olMailItem)
   
   With objmail
      .To = "someone@someplace.net.au"
      .subject = "Funds Register Update"
      .body = Application.UserName & " has updated row " & rowno & " as follows:" & vbCrLf & _
              "Registration Number: " & regno & vbCrLf & _
              "Business Unit: " & unitname & vbCrLf & _
              "Surname: " & surname1 & vbCrLf & _
              "Initials: " & initial1 & vbCrLf & _
              "Course Details: " & course1 & vbCrLf & _
              "Location: " & location1 & vbCrLf & _
              "Dates: " & date1 & vbCrLf & _
              "Training Cost: " & traincost & vbCrLf & _
              "Travel Cost: " & travelcost & vbCrLf
      .DeleteAfterSubmit = True
      .NoAging = True
      '  .Attachments.Add PathName
      .Display
   End With
   
   Set objmail = Nothing
   Set objol = Nothing
   SendKeys "%{s}", True   
   [COLOR=red]
   ' Look for the the "Spelling" window
   win_wnd = FindWindow("#32770", "Spelling")
   
   ' If the "Spelling" window is found, ...
   If win_wnd <> 0 Then
      SendKeys "{ESC}", True
      SendKeys "%{y}", True
   End If
   [/color]
   Exit Sub
SendMail_Err:
   DoEvents
End Sub

 

[ssmgr]

WinblowsMe, I never thought of using Windows API calls to detect the spell checking window. I assume I'll need to declare a variable win_wnd of type long. Thanks once again for your assistance, and have a purple star.