Compile error user defined type not define

**mukeshbaviskar** · 11-12-2013, 12:52 AM

Dear friends,
I'm unable to code the tag so I deleted it and attached a file for your kind reference. Please give me a solution.

Thanking you in anticipation.

Mukesh

**cytop** · 11-12-2013, 01:03 AM

Try adding a reference to OutLook using the Tools/References menu - if that doesn't work then try mentioning where the problem is flagged by the compiler...

To add code tags, highlight the code and click the '#' button on the toolbar.

**mukeshbaviskar** · 11-12-2013, 02:00 AM

Hi cytop,
I'm unable to quote the code tag so I deleted the code and attached a file for yor kind reference. I have already checked 'Microsoft office 12.1 object library' but the error is same.

Please go through it and give me a solution and hint to avoide such error again in future.

Thank you.

Mukesh

**cytop** · 11-12-2013, 04:13 AM

Sorry - I'm not going to debug your code for you. I said in my last post that you should

...try mentioning where the problem is flagged by the compiler.

Once you provide all the relevant information then it will be time to look at the workbook if the issue is still not obvious.

**mukeshbaviskar** · 11-12-2013, 08:12 AM

Hi CYTOP,
The problem is in the first line of code itself. Please look it and give me a solution positively.

Waiting to receive a concrete solution from you eagerly.

Thank you.

Mukesh

**HaHoBe** · 11-12-2013, 08:29 AM

Hi, Mukesh,

there is no reference for Outlook set in the sample Workbook. You would need to set that, then try the code again and you will receive another error.

You should really take some of the basics of writing/working on code. As you are pretty new to VBA I strongly recommend to put each command on a line of it´s own instead of using multiple commands in one line. And you should work with proper indention on the code so that you should have noted that there is an End If missing. Further on you don´t have a sheet Personal in the workbook.

pRow isn´t dimmed nor initialized so it holds a value of 0 and will cause another error - a very strong argument to work with Option Explicit..

Sub memberbirthday()
Dim x, i&, cnt&, OutApp As Outlook.Application
Dim Msg1 As String, Msg2 As String, Msg3 As String, Msg4 As String, Msg5 As String
Dim Msg6 As String, Msg7 As String, Msg8 As String, Msg9 As String, Msg10 As String
Dim Msg11 As String, Msg12 As String, msg13 As String
With Sheets("Sheet1")
  x = .Range("o8:Z" & .Cells(Rows.Count, "p").End(xlUp).Row + 1).Value
End With
For i = 1 To UBound(x)
  If IsDate(x(i, 2)) Then
    If Cells(7 + i, 32).Value <> "Send mail" Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
        If Len(x(i, 8)) > 2 Then
          MsgBox x(i, 1) & "'s Birthday Today!", 64
          '   Create Outlook object
          Set OutApp = New Outlook.Application
          Msg1 = "WISH YOU HAPPY BIRTHDAY!": Msg2 = "Dear " & x(i, 1) & ",": Msg3 = """Hope your Special day": Msg4 = "Brings you all that"
          Msg5 = "Your heart desires": Msg6 = "Here's wishing you a day": Msg7 = "Full of pleasant surprises!": Msg8 = "Happy Birthday"""
          Msg9 = "I have a great pleasure to convey my best wishes on the occasion of your Birth Day!"
          Msg10 = """May God offer you a very Healthy and Wealthy life ahead!!"""
          Msg11 = "With regards,": Msg12 = "Mukesh Baviskar": msg13 = "Mobile: 1234554321"
          'Create Mail Item and view before sending
          With OutApp.CreateItem(olMailItem)
            .To = x(i, 8) 'EmailAddr
            .Subject = "Happy Birthday"
            '.HTMLBody = "<center><img src='C:\Cake.jpg'></center><br><br>" 'Insert the full windows path of your image here
            '.HTMLBody = .HTMLBody & "<font color='red'>"
            .HTMLBody = "<html>" & "<center><b><i><font face=""Comic Sans MS"" size=""3"" color=""red"">" & Msg1 & "</font></i></b></center><br/><br/>" & _
                    "<center><img src='C:\Cake.jpg'></center><br><br>" & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""green"">" & Format(Date, "dd mmmm yyyy") & "</font></b></center><br/><br/>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""FF1CAE"">" & Msg2 & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""blue""><br/><br/>" & _
                    Msg3 & "<br>" & Msg4 & "<br>" & Msg5 & "<br>" & Msg6 & "<br>" & Msg7 & "<br>" & Msg8 & _
                    "</font></b></center><br><br><font color='FF1CAE'>" & Msg9 & "<br><br>" & Msg10 & "<br><br>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""red"">" & Msg11 & "<br><br>" & Msg12 & "<br><br>" & msg13 & "</font>"
            '.Attachments.Add ("c://Happy Birthday.xlsm")  ' for example
            .Display: .Send
          End With
          Set OutApp = Nothing: cnt = cnt + 1
          '-------------------  End Creating Email using outlook ---------------
        Else
          MsgBox x(i, 1) & "'s Birthday Today! & has empty E-mail address", 48
        End If    'If Len(x(i, 8)) > 2 Then
      End If
    End If
  End If
Next i
Msg1 = "The task completed successfully!": MsgBox Msg1, 64
End Sub

Mostly I wonder why you get code in your earlier threads that works and decide to sort of shredder these codes so they don´t work and then come back for help.

Ciao,
Holger

**mukeshbaviskar** · 11-12-2013, 10:26 AM

Hi Holger,
Sorry and thank you for your kind guidance. The code was working fine in my earlier posts but it takes a long time to complete the task. It scrolls from top to bottom to find the birthday on that particular date. There are thousands of members in my file and takes a long time to find the birthday. So I planned to filter the date and run the macro on that visible rows only which will complete the task within a few seconds. After working and observing the working and output the new ideas comes in mind to make the macro more perfect, clean and time saving. So I work on it continuously and come to you if I didn't get the desire output. You may observed that the same macro is modified each time and giving more perfect output.

I rename the sheet 'Personal' so the error will not come.

Just give me a idea how to set the outlook reference set in the code because the same error is I'm facing each time. I inserted the code you suggested and it also giving same error.

I like your advice to compile code in a single line to avoide error.

Thank you for kind guidance to make me perfect. I learned the little vba from the forum and guidance of you well wishers.

Thank you.

Mukesh

**mukeshbaviskar** · 11-12-2013, 01:11 PM

Hi Holger,
Good! Now I corrected the code and run the macro with no error but the mails are not delivering. The macro is running successfully with no error.

Now please see the attached file and give me advice to solve it.

Thank you.

Mukesh

**HaHoBe** · 11-12-2013, 01:19 PM

Hi, Mukesh,

but the mails are not delivering

One of the great features of the VBE is to use F8 and single step through the code to see why a condition isn´t met. You (or whoever did it) inserted

If Cells(7 + i, 32).Value <> "Send mail" Then

Maybe reflect on that line why on any birthday with that command no mail should be sent.

Ciao,
Holger

**mukeshbaviskar** · 11-12-2013, 10:24 PM

Hi Holger,
Good morning! Thank you. I edited the code and have a little progress. Now the mail is created but there is no email id in column 'z' which is in column 'm'. So the error is occurring. The code doesn't recognize the birthday of 'a' which is in row 9. It recognize only 3 birthdays 'b, I, l'. I didn't find the solution. I used f8 function but due to my visual problem I'm unable to find the error. f8 highlits the error line with yellow color. So I use f5 which shows the error with breaf description.

Please guide me.

Thank you.

Mukesh

**HaHoBe** · 11-13-2013, 01:16 AM

Hi, Mukesh,

even if the birthday is reckognized there won´t be a valid email-address as this is located in Column M whereas you work on the range starting on Column O.

Code for ThisWorkbook:

Private Sub Workbook_Open()
With Sheets("personal").Range("b4")
  If .Value < Date Then
'    Call OpenOutlook
    Call MemberBirthday_131113
    .Value = Date
  End If
End With
End Sub

Code for a standard module (here Modul1):

Sub MemberBirthday_131113()
Dim x, i&, OutApp As Object
Dim Msg1 As String, Msg2 As String, Msg3 As String, Msg4 As String, Msg5 As String
Dim Msg6 As String, Msg7 As String, Msg8 As String, Msg9 As String, Msg10 As String
Dim Msg11 As String, Msg12 As String, Msg13 As String

With Sheets("Personal")
  x = .Range("o8:Z" & .Cells(Rows.Count, "p").End(xlUp).Row + 1).Value
End With
          
Msg1 = "WISH YOU HAPPY BIRTHDAY!"
Msg3 = """Hope your Special day"
Msg4 = "Brings you all that"
Msg5 = "Your heart desires"
Msg6 = "Here's wishing you a day"
Msg7 = "Full of pleasant surprises!"
Msg8 = "Happy Birthday"""
Msg9 = "I have a great pleasure to convey my best wishes on the occasion of your Birth Day!"
Msg10 = """May God offer you a very Healthy and Wealthy life ahead!!"""
Msg11 = "With regards,"
Msg12 = "Mukesh Baviskar"
Msg13 = "Mobile:   1234554321  "

For i = 1 To UBound(x)
  If IsDate(x(i, 2)) Then
    If Cells(7 + i, 32).Value = "Send mail" Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
        If Cells(7 + i, "M") > 2 Then
          MsgBox x(i, 1) & "'s Birthday Today!", 64
          '   Create Outlook object
          Set OutApp = CreateObject("Outlook.Application")
          Msg2 = "Dear " & x(i, 1) & ","
          'Create Mail Item and view before sending
          With OutApp.CreateItem(0)
            .To = Cells(7 + i, "M") 'EmailAddr
            .Subject = "Happy Birthday"
            '.HTMLBody = "<center><img src='C:\Cake.jpg'></center><br><br>" 'Insert the full windows path of your image here
            '.HTMLBody = .HTMLBody & "<font color='red'>"
            .HTMLBody = "<html>" & "<center><b><i><font face=""Comic Sans MS"" size=""3"" color=""red"">" & Msg1 & "</font></i></b></center><br/><br/>" & _
                    "<center><img src='C:\Cake.jpg'></center><br><br>" & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""green"">" & Format(Date, "dd mmmm yyyy") & "</font></b></center><br/><br/>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""FF1CAE"">" & Msg2 & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""blue""><br/><br/>" & _
                    Msg3 & "<br>" & Msg4 & "<br>" & Msg5 & "<br>" & Msg6 & "<br>" & Msg7 & "<br>" & Msg8 & _
                    "</font></b></center><br><br><font color='FF1CAE'>" & Msg9 & "<br><br>" & Msg10 & "<br><br>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""red"">" & Msg11 & "<br><br>" & Msg12 & "<br><br>" & Msg13 & "</font>"
            '.Attachments.Add ("c://Happy Birthday.xlsm")  ' for example
            .Display
            .Send
          End With
          Set OutApp = Nothing
          '-------------------  End Creating Email using outlook ---------------
        Else
          MsgBox x(i, 1) & "'s Birthday Today! & has empty E-mail address", 48
        End If    'If Len(x(i, 8)) > 2 Then
      End If
    End If
  End If
Next i
MsgBox "The task completed successfully!", 64
End Sub

You should consider to use the Autofilter for Column AF and use "Send mail" as criteria. Any further action could be to check the number of the last visible cell to find out if any birthdays are on that day and then use a For Each and loop through the visible cells by using SpecialCells(xlCellTypeVisible) in one Column.

Ciao,
Holger

**mukeshbaviskar** · 11-13-2013, 03:29 AM

Hi Holger,
Excellent! This much I expect to speed up the task. It's working fine as per my desire. However I didn't understand the meaning of 'z' in this line:

x = .Range("o8:Z" & .Cells(Rows.Count, "p").End(xlUp).Row + 1).Value

What does this 'z' represents here?

It's just for my knowledge. The problem is solved.

Thank you for teaching and solving my problem with helpful tips to develop my skill.

Mukesh

**HaHoBe** · 11-13-2013, 07:11 AM

Hi, Mukesh,

inside a bracket with Range it´s the Column Name so you are referencing the Range starting with O8 and going to Z and the last used row.

Thanks for the feedback.

Ciao,
Holger

**mukeshbaviskar** · 11-13-2013, 08:41 AM

Hi Holger,
That means in our case it should be 'af' instead of 'z'?

Please clarify.

Thank you for guidance.

Mukesh

**HaHoBe** · 11-13-2013, 09:22 AM

Hi, Mukesh,

the data should have gone to OClumn Z when the code was written, right now it´s going over to Column AF. So basicly the answer is yes but I would avoid any hardcoding on the Columns and use the last used Column in Row 8 like

With Sheets("Personal")
  x = .Range(.Cells(8, "O"), .Cells(.Cells(Rows.Count, "p").End(xlUp).Row + 1, .Cells(8, Columns.Count).End(xlToLeft).Column)).Value
End With

But that won´t solve the problem of the email addresses not being included in the range you read into x.

Ciao,
Holger

**mukeshbaviskar** · 11-13-2013, 11:18 AM

Hello Holger,
Thank you for explanation. Now this new code has no restriction of how many columns there are to the right.

Thank you.

Mukesh

**mukeshbaviskar** · 11-13-2013, 11:49 AM

Hi Holger,
One more little problem. If there is no email id then I inserted a dash '-' sign in that cell. At this situation the program asks email id which I want to ignore by program. At this time the pop up message should pop up as follows:

MsgBox x(i, 1) & "'s Birthday Today! & has empty E-mail address", 48

How to insert this msg into the code?

Thank you.

Mukesh

**HaHoBe** · 11-14-2013, 10:09 AM

Hi, Mukesh,

I don´t get why this message doesn´t pop up in your version. If I take the workbook, alter the birthday for the first 2 entries and delete the email in Column M for the first I get that message.

You would need to clarify what else you want but again: this is not the problem on which you started the thread.

Ciao,
holger

**mukeshbaviskar** · 11-14-2013, 11:54 AM

Hi Holger,
Sorry! I agree with you that it's not a subject of this thread but in this code I'm getting error which you could understand well because you have a idea of this program.

For example:
I inserted a dash sign in cell m9 where the email id is not available. Then the program gives a error. It creates a mail with a dash sign in a 'to' field and displays the mail.

The requirement is that if the email id is not available then I insert a dash sign there for not keep the cell blank. In this situation the program should simply pop up the message 'Mr. a's birthday today! & has empty email address' and after clicking on ok continue till the end. In short the program should simply display a message with the name of a person and & has empty email address. Then continue till the end.

If this problem is solved then there is no any issue to solve.

I'm optimistic that you will definitely solve this problem and this will be the last post for this thread.

Thank you very much.

Regards,

Mukesh

**HaHoBe** · 11-14-2013, 12:08 PM

Hi, Mukesh,

Sub MemberBirthday_131114()
Dim x, i&, OutApp As Object
Dim Msg1 As String, Msg2 As String, Msg3 As String, Msg4 As String, Msg5 As String
Dim Msg6 As String, Msg7 As String, Msg8 As String, Msg9 As String, Msg10 As String
Dim Msg11 As String, Msg12 As String, Msg13 As String

Const clngSTART As Long = 8

With Sheets("Personal")
  'x = .Range("o8:af" & .Cells(Rows.Count, "p").End(xlUp).Row + 1).Value
    x = .Range(.Cells(clngSTART, "O"), .Cells(.Cells(Rows.Count, "p").End(xlUp).Row + 1, .Cells(clngSTART, Columns.Count).End(xlToLeft).Column)).Value
End With
          
Msg1 = "WISH YOU HAPPY BIRTHDAY!"
Msg3 = """Hope your Special day"
Msg4 = "Brings you all that"
Msg5 = "Your heart desires"
Msg6 = "Here's wishing you a day"
Msg7 = "Full of pleasant surprises!"
Msg8 = "Happy Birthday"""
Msg9 = "I have a great pleasure to convey my best wishes on the occasion of your Birth Day!"
Msg10 = """May God offer you a very Healthy and Wealthy life ahead!!"""
Msg11 = "With regards,"
Msg12 = "Mukesh Baviskar"
Msg13 = "Mobile:      1234554321     "

For i = 1 To UBound(x)
  If IsDate(x(i, 2)) Then
    If Cells(clngSTART + i - 1, 32).Value = "Send mail" Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
        If Cells(clngSTART + i - 1, "M") <> "-" Then
          MsgBox x(i, 1) & "'s Birthday Today!", 64
          '   Create Outlook object
          Set OutApp = CreateObject("Outlook.Application")
          Msg2 = "Dear " & x(i, 1) & ","
          'Create Mail Item and view before sending
          With OutApp.CreateItem(0)
            .To = Cells(clngSTART + i - 1, "M") 'EmailAddr
            .Subject = "Happy Birthday"
            '.HTMLBody = "<center><img src='C:\Cake.jpg'></center><br><br>" 'Insert the full windows path of your image here
            '.HTMLBody = .HTMLBody & "<font color='red'>"
            .HTMLBody = "<html>" & "<center><b><i><font face=""Comic Sans MS"" size=""3"" color=""red"">" & Msg1 & "</font></i></b></center><br/><br/>" & _
                    "<center><img src='C:\Cake.jpg'></center><br><br>" & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""green"">" & Format(Date, "dd mmmm yyyy") & "</font></b></center><br/><br/>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""FF1CAE"">" & Msg2 & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""blue""><br/><br/>" & _
                    Msg3 & "<br>" & Msg4 & "<br>" & Msg5 & "<br>" & Msg6 & "<br>" & Msg7 & "<br>" & Msg8 & _
                    "</font></b></center><br><br><font color='FF1CAE'>" & Msg9 & "<br><br>" & Msg10 & "<br><br>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""red"">" & Msg11 & "<br><br>" & Msg12 & "<br><br>" & Msg13 & "</font>"
            '.Attachments.Add ("c://Happy Birthday.xlsm")  ' for example
            .Display
            .Send
          End With
          Set OutApp = Nothing
          '-------------------  End Creating Email using outlook ---------------
        Else
          MsgBox x(i, 1) & "'s Birthday Today! & has empty E-mail address", 48
        End If    'If Len(x(i, 8)) > 2 Then
      End If
    End If
  End If
Next i
MsgBox "The task completed successfully!", 64
End Sub

Ciao,
Holger

**mukeshbaviskar** · 11-14-2013, 10:40 PM

Hi Holger,
Good morning! Excellent! Now it's working fine as per my requirement.

Really I appreciate you for your kind help and guidance.

Thank you for solving my problem with perfect output.

Regards,

Mukesh

Compile error user defined type not define

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Re: Compile error user defined type not define

Thread Information

Users Browsing this Thread

Similar Threads

Error message: "Compile error. User-defined type not defined"

compile error - user defined type not defined

VB Compile Error - User-defined type not defined

Error message: "Compile error. User-defined type not defined"

[SOLVED] Help: Compile error: type mismatch: array or user defined type expected

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions