Dead horse new stick

[scrizo]

I found some information on the forums in regards to sending emails from excel using VBA codes, and the code works fine, the issue I am running into is how do I avoid having to press send when it opens outlook for say 108 emails, and also the code seems to skip addresses. I'll post the VBA I have now. Also the layout is like this

coll# master# as subj status statusname mastersubj emails

my table is :
collectors emails status statusname

My sheet has 6 columns 2 tables to reference what the emails are and statuses.

Sub SendEmails()

  Dim Msg As String
  Dim R As Long
  Dim RetVal As Long
  Dim Subj As String
  Dim URL As String
  


      StartRow = 1
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
      
      For R = StartRow To LastRow
        Subj = Cells(R, "E").Text
        URL = "MailTo:" & Cells(R, "F").Text & "?subject=" & Subj
        RetVal = ShellExecute(0&, "open", URL, vbNullString, vbNullString, 0&)
         'Did Connection Fail? Errors are from 0 to 32
          If RetVal <= 32 Then
            Select Case RetVal
             Case 2     'SE_ERR_FNF
               Msg = "File not found"
             Case 3      'SE_ERR_PNF
               Msg = "Path not found"
             Case 5      'SE_ERR_ACCESSDENIED
               Msg = "Access denied"
             Case 8      'SE_ERR_OOM
               Msg = "Out of memory"
             Case 32     'SE_ERR_DLLNOTFOUND
               Msg = "DLL not found"
             Case 26     'SE_ERR_SHARE
               Msg = "A sharing violation occurred"
             Case 27     'SE_ERR_ASSOCINCOMPLETE
               Msg = "Incomplete or invalid file association"
             Case 28     'SE_ERR_DDETIMEOUT
               Msg = "DDE Time out"
             Case 29     'SE_ERR_DDEFAIL
               Msg = "DDE transaction failed"
             Case 30     'SE_ERR_DDEBUSY
               Msg = "DDE busy"
             Case 31     'SE_ERR_NOASSOC
               Msg = "Default Email not configured"
             Case 11     'ERROR_BAD_FORMAT
               Msg = "Invalid EXE file or error in EXE image"
             Case Else
               Msg = "Unknown error"
            End Select
            Msg = "Unable to Send Email to " & vbCrLf & "'" & MailTo & "'" & vbCrLf _
                & vbCrLf & "Error Number " & CStr(RetVal) & vbCrLf _
                & Msg
            RetVal = MsgBox(Msg, vbExclamation + vbOKOnly)
          End If
      Next R
      
End Sub

 

[scrizo]

The code works other than having to press send on a bunch of emails, will another vba script be needed and better if I am using outlook and want to use the .send function