Run-Time Error 424

[nuge725]

Hello All,

Can someone please help me with this? I am trying to write the code to send Outlook emails from Excel. Ultimately, what I would like to do is have it automatically email 90 days before the date in the cell. However, I figured I would try this first to make sure that I have everything working properly...I'm just learning how to do this. Nevertheless, when I go to run the code below, I get a "Run-Time Error 424: Object Required" message for this line:

TrainingDate = Format(cell.Offset(0, 1).Value.["mm/dd/yy"])

Any help that can be given would be greatly appreciated. Also, if you either see any other errors, or know how to set this up to send on a certain date, that advice is welcome as well. Please let me know if you need more information, and thanks in advance.

Ryan

Sub SendEmail()
    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String
    
    Set OutlookApp = New Outlook.Application
    
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "*@*" Then
            Subj = "Training Dates"
            Recipient = cell.Offset(0, -1).Value
            EmailAddr = cell.Value
            TrainingDate = Format(cell.Offset(0, 1).Value.["mm/dd/yy"])
            
            Msg = "Dear" & Recipient & vbCrLf & vbCrLf
            Msg = Msg & "Your TRAINING TYPE training is due on:"
            Msg = Msg & TrainingDate & vbCrLf & vbCrLf
            Msg = Msg & "NAME" & vbCrLf
            Msg = Msg & "TITLE"
            
            Set MItem = OutlookApp.CreateItem(olMailItem)
            With MItem
                .To = EmailAddr
                .Subject = Subj
                .Body = Msg
                .Send
            End With
        End If
    Next
End Sub

 

[VoG]

I don't know if this is the only problem but it should be like this

TrainingDate = Format(cell.Offset(0, 1).Value, "mm/dd/yy")

 

[nuge725]

VoG,

I tried that and I kept getting a "Compile Error: Expected: Identifier or Bracketed Expression" message, so I tried adding the brackets and that resolved that issue.

 

[nuge725]

Nevermind...that fixed it. I found the problem I had, which was delivering the Compile Error was that I had a period instead of comma. Thanks VoG!!!