Big but maybe simple help is needed!
I am using excel on Windows 10 with Outlook to send emails.
My worksheet looks as per image, where I have some inputs (example email, item, company...) and a cell called "Trigger Column (templates)" from where I set a macro in which each time I select a new template from the dropdown list, the "Status" column will change from "sent!" in "to be sent".
What I want to do not is to send ONLY the templates that have in status "to be sent!".
Other than "Worksheet", I have more sheets called "Sender" and "Templates".
As you can see on "Templates" sheet I have various columns. Under the column "Template content" I have some variables that can be found in the sheet "Worksheet":
Hi <Name>,
I hope you are well.
We would like to order from you <Quantity> <Color> <Item> at the agreed price of <Price> EUR.
We need to receive the order no later than <Deadline>.
<Signature>
What I would really love to do is to be able to send only to the rows marked as "to be sent!" the email as per the template, replacing any variable with the correct data.
What currently have in the Marco is:
Sub CommandButton1_Click()
ActiveWorkbook.Save
Dim sh As Worksheet
Dim rw As Range
Set sh = ActiveSheet
Dim oShell
Dim oExec
Set oShell = CreateObject("WScript.Shell")
rowIndex = 0
statusIndex = getColIndexByTitle("Status")
script2 = "javaw -jar " + """" + Application.ActiveWorkbook.Path + "/SupplierMailSender.jar" + """ " + """" + Application.ActiveWorkbook.FullName + """"
Set oExec = oShell.Exec(script2)
Debug.Print script2
x = oExec.StdOut.ReadLine
Set ws = Application.ActiveSheet
Dim WrdArray() As String
WrdArray() = Split(x)
For i = LBound(WrdArray) To UBound(WrdArray)
rowIndex = CInt(WrdArray(i))
ws.Cells(rowIndex, CInt(statusIndex)).Value = "sent!"
Next i
Debug.Print x
ActiveWorkbook.Save
End Sub
Public Function getColIndexByTitle(rowName As String) As Integer
titleRowIndex = 1
col = 1
Set ws = Application.ActiveSheet
Do While True
If ws.Cells(titleRowIndex, col).Value = "" Then
Exit Do
End If
If ws.Cells(titleRowIndex, col).Value = rowName Then
getColIndexByTitle = col
Exit Do
End If
col = col + 1
Loop
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
statusCol = getColIndexByTitle("Status")
templateCol = getColIndexByTitle("Trigger Column (templates)")
If (templateCol = Target.Column) Then
For Each cel In Target ' do the next steps for each cell that was changed
Set ws = Application.ActiveSheet
ws.Cells(cel.Row, statusCol).Value = "to be sent!"
Next cel
End If
End Sub
The problem is that I took from an example this but I do not have:
script2 = "javaw -jar " + """" + Application.ActiveWorkbook.Path + "/SupplierMailSender.jar" + """ " + """" + Application.ActiveWorkbook.FullName + """"
Set oExec = oShell.Exec(script2)
Debug.Print script2
x = oExec.StdOut.ReadLine
I guess this is the part that should be replaced to create the Outlook email. I am unsure if I can send it directly from VBA or if the email needs a Java file. In that case, I do not know Java code and I need some help to send the email correctly. I really hope an Angel can help!!!
