Getting compile error 'type mismatch'

[mso3]

Hi friends,

I’m getting compile error ‘type mismatch’

I want to send mails on the occasion of birthday using mail merge.

Any help will be highly appreciated.

Thanking you in anticipation.

[Trebor76]

Hi mso3,

There were a number of errors:

• Wrong declaration of variables - i needs to a Long variable not an Object
• Missing variables - OutApp was not defined (this is why Option Explicit is so useful)
• There were a number of missing End If statements to close off your IF clauses (this is why indenting is so useful)

The code below now starts without issue but I can't run it from my machine.

Regards,

Robert

Option Explicit
Sub SendDocAsMsg()

    Dim x
    Dim Msg1 As String
    Dim wd As Word.Application
    Dim doc As Word.Document
    Dim itm As Outlook.MailItem
    Dim ID As String
    Dim blnWeOpenedWord As Boolean
    Dim i As Long
    Dim OutApp As Object
    On Error Resume Next
    
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
        blnWeOpenedWord = True
    End If
    
    Set doc = wd.Documents.Open(Filename:="C:\Birthday.docx", ReadOnly:=True)
    Set itm = doc.MailEnvelope.Item
    
    Worksheets("Access").Activate
    
    Const clngSTART As Long = 8
    
    With Sheets("Access")
        x = .Range(.Cells(clngSTART, "O"), .Cells(.Cells(Rows.Count, "P").End(xlUp).Row + 1, .Cells(clngSTART, Columns.Count).End(xlToLeft).Column)).Value
    End With
    
    For i = 1 To UBound(x)
        If IsDate(x(i, 2)) Then
            If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
                If Cells(clngSTART + i - 1, "R") <> "-" Then
                    MsgBox x(i, 1) & "'s Birthday Today!", 64
                    'Create Outlook object
                    Set OutApp = CreateObject("Outlook.Application")
                    Msg1 = x(i, 1)
                    'Create Mail Item and view before sending
                    With OutApp.CreateItem(0)
                        .To = Cells(clngSTART + i - 1, "R")
                        .BCC = ""
                        .Subject = "Happy Birthday"
                        .Send
                    End With
                    doc.Close wdDoNotSaveChanges
                    If blnWeOpenedWord Then
                        wd.Quit
                    End If
                End If
            End If
        End If
    Next i
    
    Set doc = Nothing
    Set itm = Nothing
    Set wd = Nothing
    
    MsgBox "Done!", 64

End Sub

[mso3]

Hi Robert,

Thank you very much.
Now the mails are delivering but the message body is blank.
Everything else is ok.
How to get a message in message body from word document?

Thank you and have a nice day.

[Trebor76]

How to get a message in message body from word document?

Not really sure - perhaps try setting up a bookmark in Word which the code can then use as the body of the email. Should be plenty of references on the net about this.

Good luck with it.

Robert

[mso3]

Hi,

I search on the net but didn't find a proper solution for it.

Friends, Please suggest me a amendment in code to get a message in message body from main word document.

Waiting to receive a solution soon.

Thanking you,

[mso3]

Hi Robert,
Now I'm getting message in message body but loosing the formatting and not getting a name of person after the word 'Dear ,'.

Option Explicit

Sub SendDocAsMsg()

Dim x
Dim Msg1 As String
Dim wd As Word.Application
Dim doc As Word.Document
Dim itm As Outlook.MailItem
Dim ID As String
Dim blnWeOpenedWord As Boolean
Dim i As Long
Dim OutApp As Object
On Error Resume Next

Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
blnWeOpenedWord = True
End If
Set doc = wd.Documents.Open _
(Filename:="C:\BIRTHDAY.docx", ReadOnly:=True)
Set itm = doc.MailEnvelope.Item

Worksheets("Access").Activate

Const clngSTART As Long = 8

With Sheets("Access")
x = .Range(.Cells(clngSTART, "O"), .Cells(.Cells(Rows.Count, "P").End(xlUp).Row + 1, .Cells(clngSTART, Columns.Count).End(xlToLeft).Column)).Value
End With

For i = 1 To UBound(x)
  If IsDate(x(i, 2)) Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
If Cells(clngSTART + i - 1, "R") <> "-" Then
MsgBox x(i, 1) & "'s Birthday Today!", 64
'   Create Outlook object
Set OutApp = CreateObject("Outlook.Application")
Msg1 = x(i, 1)
'Create Mail Item and view before sending
'With OutApp.CreateItem(0)
With itm
.To = Cells(clngSTART + i - 1, "R")
.BCC = ""
.Subject = "Happy Birthday"
.Send
End With
doc.Close wdDoNotSaveChanges
If blnWeOpenedWord Then
wd.Quit
End If
End If
End If
End If
Next i

Set doc = Nothing
Set itm = Nothing
Set wd = Nothing

MsgBox "Congratulation! The mails sent successfully.", 64

End Sub

Guidance please!

Thank you and have a nice time.