Simple Email through VBA to send a range of cells

[ARW17]

I've been using the following, which used to work and somehow I must have changed something that's making it get stuck. I'll see the email (just like I want it) pop up in Outlook asking me if I want to send it. At that point, everything is frozen and I have to force Excel to shut down. When it was working, I never saw the email pop up on the screen for me to hit send, it would just go straight to my inbox.

Sheets("Self-Service Schedule").Select
ActiveWorkbook.Save
ActiveSheet.Range("A1:D75").Select
ActiveWorkbook.EnvelopeVisible = True


With ActiveSheet.MailEnvelope
.Introduction = Worksheets("Worksheet for S-S").Range("H7")
.Item.To = Worksheets("Worksheet for S-S").Range("H3")
.Item.Subject = Worksheets("Worksheet for S-S").Range("H7")
.Item.Send
End With

Any help would be greatly appreciated!

 

[Kenneth Hobson]

I have never seen that but those routines don't work for me. Maybe it is because we use Outlook version 14.0.0.7172 and Excel version 14.0.

See Ron's RangeToHTML routine.

For a quickie:

'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm

'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
Sub Main()
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  Dim a() As Variant, r As Range, c As Range
  
  Set olApp = New Outlook.Application
  
  Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
  For Each c In r
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
      .To = c.Value                           'Column B
      .Subject = "Pin No. " & c.Offset(, 3)   'Column E
      .Body = c.Offset(, 1)                   'Column C
      .Display
      '.Send
    End With
  Next c
  
  Set olMail = Nothing
  Set olApp = Nothing
End Sub