VBA Auto mail problem

[criper]

Hi,

I have a mail macro that automatically sends email based on a cell value in Excel.
So what this is supposed to do is auto mail when cell C2 is larger than 0. This is working as it should.

The problem here is that the values are gathered with formulas, "=COUNTIF(Utleveranser.Data!B:B, 1)". So the data in cell C2 isn't inserted manually.
When the data is inserted manually, the automail works. But if the value gets inserted through the formula, the mail function doesn't work.

Any ideas?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C2"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 0 Then
Call Indiska
End If
End If

Public Sub Indiska()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Indiska"

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Subject"
.Body = strbody
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[louisH]

Hi.

There's Something I don't get. You have a formula in C2 which does your calculation, but you want the Worksheet_Change module to trigger when C2 is changed ?

The Worksheet_Change module will only trigger when the user manually modifies a cell in the worksheet

Anyways try to add :

Target.Worksheet.Calculate

before

Call Indiska

 

[louisH]

The Worksheet_Change module will only trigger when the user manually modifies a cell in the worksheet

I would recommend placing this in the "Utleveranser.Data" worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column <> 2 Or Target.Columns.Count > 1 Then Exit Sub
    
    If WorksheetFunction.CountIf(Target.Worksheet.Range("B:B"), 1) > 0 Then Call Indiska
    
End Sub

 

[criper]

Hi louisH,

What I want is that the automail works although the data is inserted with a formula, not manually. Is that possible?

This is because I have a database connected to Excel, and the automail function needs to work based on values 0 or 1, which represents entries or no entries in the database.

Example:

I have some outgoing transactions for a customer stored in the database, then the value in C2 will be "1", and an automail is mailed to one of my employees.
When the transaction is done, it is no longer stored in the database, then the value will become "0".

 

[criper]

Hi again louisH,

It got solved using Worksheet_Calculate().

But not multiple mail gets sent, I'll try to figure out why.

Thanks for your help!

 

[criper]

Now* multiple mail gets sent.

 

[louisH]

Now* multiple mail gets sent.

I'm happy to see you found your answer sorry for not figuring out what the problem was.