Hi,

If Outlook isnt open, when the macro run, it will prompt me some code address error.

click on debug , it point me to this line of code:

Set OutMail = OutApp.CreateItem(0)

Work when my outlook is open before excel run the macro.


Any idea?

Sub Workbook_Open()

' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim SafeItem As Object
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 
    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange

    Set OutApp = CreateObject("Outlook.Application")
    Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
  
    
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
  SafeItem.Item = OutMail 'set Item property
SafeItem.Recipients.Add "1234@1254.com"
SafeItem.Recipients.ResolveAll

    On Error Resume Next
    With SafeItem
        .To = "1234@1254.com"
        .CC = ""
        .BCC = ""
        .Subject = "BT Direct Disc " & Format(Now, "dd-mmm-yy hh:mm")
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing

Application.OnTime Now + TimeValue("00:0:05"), "Save_Exit"

End Sub