I have an issue with sending email using VBA. So I have checkboxes and items in the sheet and I want to send items to email when the checkboxes are selected. If not selected, then do nothing. I have this error that says 'Subscript out of range'. Im not sure how to fix. This is the image of the spreadsheet that Im testing on and here's my code:
Private Sub sendEmail(arrType, arrItem, arrQuantity, arrUnit)
Dim i As Integer
Dim objOutlook As Object
Set objOutlook = CreateObject("outlook.application"
Dim ws As Worksheet
Dim strSubject As String
Dim strBody As String
Dim strType As String
Dim strItem As String
Dim strQuantity As String
Dim strUnit As String
Dim strTable As String
Dim strHTML As String
Set ws = ThisWorkbook.Worksheets("Data")
strSubject = "Testing"
strBody = "<html>"
strBody = strBody & "Please see the order details below for your reference:<br><br>"
strTable = "<br><table border = 2><tbody>"
strTable = strTable & "<tr>"
strTable = strTable & "<th align = center> Type</th>"
strTable = strTable & "<th align = center> Item</th>"
strTable = strTable & "<th align = center> Quantity</th>"
strTable = strTable & "<th align = center> unit</th>"
strTable = strTable & "<tr/>"
For i = 4 To UBound(arrType)
strType = arrType(i)
strItem = arrItem(i)
strQuantity = arrQuantity(i)
strUnit = arrUnit(i)
strTable = strTable & "<tr><td>" & strType & "</td>"
strTable = strTable & "<td>" & strItem & "</td>"
strTable = strTable & "<td>" & strQuantity & "</td>"
strTable = strTable & "<td>" & strUnit & "</td></tr>"
Next
strTable = strTable & "</tbody></table><br>"
strHTML = strBody & strTable & "</html>"
If MsgBox("Are you sure you want to submit? ", vbYesNo, "Submit Confirmation") = vbYes Then
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(0)
With objEmail
.To = ""
.Subject = "testing"
.HTMLBody = strHTML
.Display
.Send
End With
MsgBox "Thanks for the order. Your order details are sent successfully.", vbxOKOnly, "Operation Successful"
Else
Exit Sub
End If
End Sub
Private Sub itemStored(arrType, arrItem, arrQuantity, arrUnit)
Set ws = ThisWorkbook.Worksheets("Data")
Dim i As Long
Dim cb As CheckBox
For Each cb In CheckBoxes
If cb.Value = 1 Then
arrType(i) = ws.Cells(i + 4, "I").Value
arrItem(i) = ws.Cells(i + 4, "I").Value
arrQuantity(i) = ws.Cells(i + 4, "I").Value
arrUnit(i) = ws.Cells(i + 4, "I").Value
i = i + 1
End If
Next
End Sub
Private Sub cmdbtnShow_Click()
OrderForm.Show
End Sub
Private Sub CommandButton2_Click()
Dim arrType() As Variant
Dim arrItem() As Variant
Dim arrQuantity As Integer
Dim arrUnit As String
Call itemStored(arrType, arrItem, arrQuantity, arrUnit)
Call sendEmail(arrType, arrItem, arrQuantity, arrUnit)
End Sub
When the checkboxes are selected, items on the left hand side will send to email. If not, noting will happen.I tried making arrType and arrItem correspond to sendEmail but still doesn't work.
Aucun commentaire:
Enregistrer un commentaire