Using VB to send email from excel list RRS feed

  • Question

  • Hi  i have the following code below. what i am trying to do is after i have exported my list from outlook to excel, i want excell to use VB to create a reply to the message, and include the name. when i export from outlook i export the email address and the name , it comes usually in the folloing format ,  last,first. i need the program to add the name to the body of the email. i keep getting some errors like runtime error 424.

    could any one please give me a hand this is what i have so far

    Private Sub cmd_Send_emails_Click()
    Dim xlwkbk As Excel.Workbook ' excel workbook object
    Dim olApp As Outlook.Application
    Dim olmail As MailItem
    Dim row As Long
    Dim olMailItem
    Dim MailId As String
    Dim Name As String
    Dim Details As String
    Dim subject As String
    Dim i As Integer
    Dim n As Integer

    MailId = wkbk.Cells(1, n)
    Name = Cells(1, i)
    n = 1
    i = 2

    If wkbk.Cells.[1,1] Is Not Empty Then
    With olmail
    .To = MailId
    .subject = "Thank you for e-mail"
    .Body = "Dear" < Name > vbCrLf & "Thank you for your email."
    .Body = .Body & "A medical Device and Equipment sales Rep (DME Rep) Career is Certainly"
    .Body = .Body & "an excellent choice as it is rated the Highest Paying sales Rep Position"
    .Body = .Body & "and also healthcare is the fastest growing industry in The United States."
    .Body = .Body & "We will keep your info on file but it is also very important to contact"
    .Body = .Body & "numerous companies to assure good job Search results. With that being said"
    .Body = .Body & "we also encourage you to look at thousands of other job opportunities at"
    .Body = .Body & " You will need to sign up with the FREE NAMSR"
    .Body = .Body & "Employment Center in order to submit your applications and you can post"
    .Body = .Body & "your resume all for FREE. We do strongly recommend that you also include"
    .Body = .Body & "medical device sales training or education to attract medical companies."
    .Body = .Body & "It is also vital for Entry level candidates to show sales ability with"
    .Body = .Body & "medical knowledge to qualify for interviews. If you have any questions"
    .Body = .Body & "just email me back and I will be happy to respond to you. Thank you"
    .Body = .Body & "for your time and response" & vbCrLf & " Diane Crawford - RMSR Coordinator"
    .Body = .Body & vbCrLf & "NAMSR" & vbCrLf & " NAtional Association of Medical" & vbCrLf & " Sales Representatives" & vbCrLf & ""
    End With
    n = n + 1
    i = i + 1

    End If


    End Sub

    Friday, January 20, 2012 5:53 PM


  • Mig,

    VBA error 424 indicates an invalid or uninstantiated object.  Your code shows that you're dimensioning a workbook variable:

    "Dim xlwkbk As Excel.Workbook"

    But in the body of the subroutine, you keep referring to "wkbk", which was not declared.

    Could that be it?



    Friday, January 20, 2012 7:29 PM