ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   E-Mail Importance Flag Q (https://www.excelbanter.com/excel-programming/396187-e-mail-importance-flag-q.html)

Sean
E-Mail Importance Flag Q
 
I use the code, part of which is extracted below, kindly written and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to set
the Importance to High (i.e. value =2) IF a value is 1 in SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks


Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With


'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr


Set OutMail = Nothing
Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

JW[_2_]
E-Mail Importance Flag Q
 
Change this line:
..Importance = 2

To this:
If Sheets("SheetB").Range("AA1").Value 1 Then
.Importance = 2
Else
.Importance = 1
End If

This will cause any value greater than one to force an importance of
2. Any value equal to or less than one will cause an implrtance of 1.

HTH
-Jeff-

Sean wrote:
I use the code, part of which is extracted below, kindly written and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to set
the Importance to High (i.e. value =2) IF a value is 1 in SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks


Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With


'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr


Set OutMail = Nothing
Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Sean
E-Mail Importance Flag Q
 
On Aug 23, 5:03 pm, JW wrote:
Change this line:
.Importance = 2

To this:
If Sheets("SheetB").Range("AA1").Value 1 Then
.Importance = 2
Else
.Importance = 1
End If

This will cause any value greater than one to force an importance of
2. Any value equal to or less than one will cause an implrtance of 1.

HTH
-Jeff-



Sean wrote:
I use the code, part of which is extracted below, kindly written and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to set
the Importance to High (i.e. value =2) IF a value is 1 in SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks

Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub- Hide quoted text -

- Show quoted text -

Jeff, thanks for that

Sean
E-Mail Importance Flag Q
 
On Aug 24, 7:55 am, Sean wrote:
On Aug 23, 5:03 pm, JW wrote:





Change this line:
.Importance = 2

To this:
If Sheets("SheetB").Range("AA1").Value 1 Then
.Importance = 2
Else
.Importance = 1
End If

This will cause any value greater than one to force an importance of
2. Any value equal to or less than one will cause an implrtance of 1.

HTH
-Jeff-

Sean wrote:
I use the code, part of which is extracted below, kindly written and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to set
the Importance to High (i.e. value =2) IF a value is 1 in SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks

Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub- Hide quoted text -

- Show quoted text -

Jeff, thanks for that- Hide quoted text -

- Show quoted text -

How would I tweak the obove, to do as before except set the importance
to high IF AA11 or AA2 1? I could do it within a formula, but not in
code

Thanks

Sean
E-Mail Importance Flag Q
 
On Aug 24, 2:56 pm, Sean wrote:
On Aug 24, 7:55 am, Sean wrote:





On Aug 23, 5:03 pm, JW wrote:

Change this line:
.Importance = 2

To this:
If Sheets("SheetB").Range("AA1").Value 1 Then
.Importance = 2
Else
.Importance = 1
End If

This will cause any value greater than one to force an importance of
2. Any value equal to or less than one will cause an implrtance of 1.

HTH
-Jeff-

Sean wrote:
I use the code, part of which is extracted below, kindly written and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to set
the Importance to High (i.e. value =2) IF a value is 1 in SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks

Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub- Hide quoted text -

- Show quoted text -

Jeff, thanks for that- Hide quoted text -

- Show quoted text -

How would I tweak the obove, to do as before except set the importance
to high IF AA11 or AA2 1? I could do it within a formula, but not in
code

Thanks- Hide quoted text -

- Show quoted text -

I have modified what I was trying to achieve by adding cells AA1+AA2
and if the Result is 0 then ustext "No" otherwise "Yes". With a "No"
returned in cell AA3 I require the Importance to be set to 2 (i.e.
High), however my value in AA3 is "Yes" but it still sets the
Importance level to High.

What have I done wrong?

Thanks


If Sheets("SheetB").Range("AA3").Value = "No" Then
.Importance = 2
Else
.Importance = 1
End If
.Send
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%S"
End With

Dana DeLouis
E-Mail Importance Flag Q
 
With a "No" returned in cell AA3
I require the Importance to be set to 2

Just another option:

..Importance = 1 - ([AA3] = "No")

--
HTH :)
Dana DeLouis


"Sean" wrote in message
oups.com...
On Aug 24, 2:56 pm, Sean wrote:
On Aug 24, 7:55 am, Sean wrote:





On Aug 23, 5:03 pm, JW wrote:

Change this line:
.Importance = 2

To this:
If Sheets("SheetB").Range("AA1").Value 1 Then
.Importance = 2
Else
.Importance = 1
End If

This will cause any value greater than one to force an importance of
2. Any value equal to or less than one will cause an implrtance of
1.

HTH
-Jeff-

Sean wrote:
I use the code, part of which is extracted below, kindly written
and
provided by Ron de Bruin,
which E-Mails a sheet called "e-Trip", how would I tweak this to
set
the Importance to High (i.e. value =2) IF a value is 1 in
SheetB!AA1.
If the value in SheetB!AA1 is <1, then Importance should be set as
Normal (i.e. value = 1)

Thanks

Sub Mail_Report()

(code here)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("E-Trip").Range("BA2").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("E-
Trip").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = False
.Importance = 2
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub- Hide quoted text -

- Show quoted text -

Jeff, thanks for that- Hide quoted text -

- Show quoted text -

How would I tweak the obove, to do as before except set the importance
to high IF AA11 or AA2 1? I could do it within a formula, but not in
code

Thanks- Hide quoted text -

- Show quoted text -

I have modified what I was trying to achieve by adding cells AA1+AA2
and if the Result is 0 then ustext "No" otherwise "Yes". With a "No"
returned in cell AA3 I require the Importance to be set to 2 (i.e.
High), however my value in AA3 is "Yes" but it still sets the
Importance level to High.

What have I done wrong?

Thanks


If Sheets("SheetB").Range("AA3").Value = "No" Then
.Importance = 2
Else
.Importance = 1
End If
.Send
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%S"
End With


All times are GMT +1. The time now is 11:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com