0

I was provided an existing VBA code when I first started a new job that was created by the employee before me. Unfortunately, it would not run when I first attempted to run it and the previous employee is unreachable.

My goal is to take information from an existing Excel spreadsheet and send a mass email to those on the spreadsheet.

I have attempted to edit the code several times and have taken it to the IT department at a local university, but it will not run once I get to my work computer. Currently, the error code reads:

"Compile error: user-defined type not defined"

Any assistance on this matter would be greatly appreciated. I have copied the code below: 

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

    Dim olApp As Outlook.MailItem
    Set olApp = olApp.createitem(olMailItem)

    olMail.To = what_address
    olMail.CC = "someone@nogo.net"
    olMail.Subject = subject_line
    olMail.bodyformat = olFormatHTML
    olMail.htmlbody = mail_body
    olMail.attachments.Add ("C:\Users\129020\Desktop\90DayNotice\RecertWorkshopFlyerFY17")
    olMail.attachments.Add ("C:\Users\129020\Desktop\90DayNotice\RecertApplicationFY17.pdf")
    olMail.body = mail_body

    olMail.Send

End Sub

Sub SendMassEmail()

    row_number = 1

    Do
    DoEvents
        row_number = row_number + 1
        Dim mail_body_message As String
        Dim full_name As String
        Dim Company_Name As String

        mail_body_message = Sheet8.Range("I2")
        full_name = Sheet8.Range("B" & row_number)
        Company_Name = Sheet8.Range("C" & row_number)
        mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
        mail_body_message = Replace(mail_body_message, "replace_company_here", Company_Name)


         'MegBox (Sheet.Range("J3"))
          Call SendEmail(Sheet8.Range("D" & row_number), "RECERTIFICATION APPLICATION 90 DAY NOTICE", mail_body_message)
    Loop Until row_number = 18

MsgBox "Complete"

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
SCJ10
  • 9
  • 2
  • Have you added reference to the Outlook object model? If not, `Outlook.MailItem` declaration will raise that sort of error. – David Zemens Mar 07 '18 at 16:59
  • https://stackoverflow.com/questions/5349580/compiler-error-user-defined-types-not-defined – David Zemens Mar 07 '18 at 17:00
  • https://stackoverflow.com/questions/3119207/sending-http-requests-with-vba-from-word – David Zemens Mar 07 '18 at 17:01
  • @DavidZemens I added the Outlook reference but it is just giving me the "complete" box but is not actually sending the emails. – SCJ10 Mar 07 '18 at 17:18
  • you're going to need to actually use an instance of Outlook. please look for other similar questions here, and revise your question with SPECIFIC problem (s) since the scope has changed from your initial ask. – David Zemens Mar 07 '18 at 17:25
  • See [this](https://stackoverflow.com/questions/17973549/ms-access-vba-sending-an-email-through-outlook/17975507#17975507) for example. You need to use `Outlook.Application` class *in addition to* the `Outlook.MailItem`. – David Zemens Mar 07 '18 at 17:28
  • You cannot use the same string for both the text and Html bodies. A text body will use linefeeds to space out the text. An Html body will ignore linefeeds. More correctly it will treat any sequence of whitespace characters (spaces, carriage returns, linefeeds and so on) as a single space. If ¶ represents linefeed, the text body might be: "Dear John¶¶Please see attached.¶¶Regards Jane" while the Html body might be: "

    Dear John

    Please see attached.

    Regards Jane

    ". You need to decide which you have.     – Tony Dallimore Mar 08 '18 at 13:39

0 Answers0