Help with VBA

[Prariedog13]

How do I make this program work for every row in the spreadsheet?

[wanderingfool2]

Try modifying something similar to this

'Sub Mail_small_Test_Outlook()
Sub Mail_small_Test_Outlook(processRow as integer)


'xMailBody = (Rang("D1").Value)
xMailBody = (Rang("D" & processRow).Value)

'.To = (Range("C1").Value)
.To = (Range("C" & processRow).Value)

Then you can do

Mail_small_Test_Outlook 1

Mail_small_Test_Outlook 2

To send rows 1 and 2...but then you can just modify your initial Sub to loop all rows (I'm too lazy to actually try this...but the theory of it works I think)

[Prariedog13]

17 hours ago, wanderingfool2 said:

Try modifying something similar to this

'Sub Mail_small_Test_Outlook()
Sub Mail_small_Test_Outlook(processRow as integer)


'xMailBody = (Rang("D1").Value)
xMailBody = (Rang("D" & processRow).Value)

'.To = (Range("C1").Value)
.To = (Range("C" & processRow).Value)

Then you can do

Mail_small_Test_Outlook 1

Mail_small_Test_Outlook 2

To send rows 1 and 2...but then you can just modify your initial Sub to loop all rows (I'm too lazy to actually try this...but the theory of it works I think)

Sorry about late reply, couldn't get to this all day. I got this far, but I dont really understand what the last steps are: what is Mail_small_Text_Outlook 1 ? Totally new to vba, just copied a simple email thing and modified it from what I could find on the internet. Here is an updated version of the code, and a picture of the spreadsheet if that helps too. Edit: also how would I modify the IsEmpty lines at the top?

Edited February 28, 2020 by Prariedog13
Added more info

[jdfthetech]

This is a vba script I use in excel that clears certain characters from a selected range.
It should give you a good idea of what to change:

https://github.com/jdfthetech/excelValueCleanse/blob/master/cleanBlanksSelection.vba

 

edit:

to be clear, this just copies the items back onto themselves as values to clear out special chars that get mixed in with bad data exports

Edited February 28, 2020 by jdfthetech
clarification

[wanderingfool2]

If you get rid of the call Mail_small_Text_Outlook

And replace with

Mail_small_Text_Outlook 1

(That sends the first line).

 

Search for "vba parameters" (Sorry, almost out of time so hoping that will point you in the right direction of how it is being used).  Effectively using "1" sets the processRow variable to "1".

 

If you copy paste your code (and use codetags...the "<>"  button in the form post) it makes things easier for people responding.

 

Also, your isEmpty should be more in format of

IF isEmpty(Range("A1")) = TRUE THEN
	exit sub 'This makes it a lot more readable/more straight forward
END IF

 

 

[Prariedog13]

23 hours ago, wanderingfool2 said:

If you get rid of the call Mail_small_Text_Outlook

And replace with

Mail_small_Text_Outlook 1

(That sends the first line).

 

Search for "vba parameters" (Sorry, almost out of time so hoping that will point you in the right direction of how it is being used).  Effectively using "1" sets the processRow variable to "1".

 

If you copy paste your code (and use codetags...the "<>"  button in the form post) it makes things easier for people responding.

 

Also, your isEmpty should be more in format of

IF isEmpty(Range("A1")) = TRUE THEN
	exit sub 'This makes it a lot more readable/more straight forward
END IF

 

 

Sorry I totally didn't use your code, couldn't figure out how to get it to work, but I made this, and I still dont understand why it doesn't work, it just crashes every time I edit a cell.

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

X = 1

Start:

Application.Wait (Now + TimeValue("00:00:10"))

If IsEmpty(Range("H2").Value) = True Then

GoTo Start

Else: GoTo Start1

End If



Start1:

X = X + 1

Dim xOutApp As Object

Dim xOutMail As Object

Dim xMailBody As String

Set xOutApp = CreateObject("Outlook.Application")

Set xOutMail = xOutApp.CreateItem(0)

xMailBody = (Range("E" & X).Value)

On Error Resume Next

With xOutMail

.To = (Range("D" & X).Value)

.CC = ""

.BCC = ""

.Subject = (Range("C" & X).Value)

.Body = xMailBody

.Display 'or use .Send

End With

On Error GoTo 0

Set xOutMail = Nothing

Set xOutApp = Nothing

If X > 49 Then

GoTo Done

End If

Done:

End Sub

Edited March 1, 2020 by Prariedog13