Macro not working while sending email

Printable View

09-26-2016, 08:40 AM
Dah

2 Attachment(s)

Macro not working while sending email

Hi

I am new to this forum. The macro was not working. Can anybody help me. I asked in some forum but not get the response.

From the attached excel file, I would like to create a macro for sending mail to the Product Incharge hoder (column C) keeping cc to Manager 2 and Manager 3 when the Product sampling date (Column G) is matching with current date.

In the body of mail, a snapshot will be attached as reference with data populated from column A, B and H-I like below

Attachment 17179

Please help me to debug the code available within excel file
Thank you very much in advance
09-26-2016, 09:26 AM
offthelip

I can see an immediate problem with your "testing " Module.
the line:
[vba] If (ActiveWorkbook.Worksheets(sht1).Cells(i, 8)) = Now Then
[/vba]
will never trigger , this is because the function "Now" gives you date and time
so the value in cells(i,8) which is just a date will never equate to "Now"
You need to compare the integer values then they will match :

[Vba]If (Int(ActiveWorkbook.Worksheets(sht1).Cells(i, 8))) = Int(Now) Then[/VBA]

note : I haven't looked at the email code
09-26-2016, 09:47 AM
Dah

Thank you very much for your prompt response in guiding me.
It's working now. Can you please help me to write code how can I take a copy of the respective row (only for column A-B, H-I) which I attach in the beginning.
09-26-2016, 03:54 PM
offthelip

I am not sure what you are asking for, do you want to include the cells from columns A,B H and I in the message, or do you want to copy them somewhere else and save them, what do you mean by "Attach in the beginning"
09-26-2016, 06:55 PM
Dah

1 Attachment(s)

In the body of email, I want to include the data from cells A, B, I-N when it matches with current date.
Snap for your reference.

Attachment 17184
09-27-2016, 12:48 AM
offthelip

try something like this:
Note , I have loaded the complete row of data when it matches into a variant array ( datavar) the reason for doing this is that every access to the excel spreadsheet takes a fair amount of time when the macor runs, so it is much quicker to load the entire row of data in an array and then reference the array. It is also quicker to type since datavar is far fewer letters than "Activeworkbooks.worksheets(sht1)" . I have also done the same thing with the headers.
Note since I don't use Outlook, I commented out that bit and just debut printed the string.

[vba]Private Sub Fire_mail(x As Long, str As String)

Dim App As Object
Dim item As Object
Dim msg As String
Dim esubject As String
Dim ebody As String
Dim sendto As String
Dim ccto As String
Dim datavar As Variant
Dim headings As Variant
Dim i As Integer

Dim sht1
sht1 = "Prod Update"
On Error GoTo ***

If (str = "Alert Email") Then
headings = ActiveWorkbook.Worksheets(sht1).Range(Cells(1, 1), Cells(1, 20))
datavar = ActiveWorkbook.Worksheets(sht1).Range(Cells(x, 1), Cells(x, 20))
esubject = datavar(1, 1) & " for checking"
msg = "<HTML>"
msg = ""
msg = msg & "Dear Author" & ", "
msg = msg & "This is to inform you "
msg = msg & headings(1, 1) & ": " & datavar(1, 1) & ", "
msg = msg & headings(1, 2) & ": " & datavar(1, 2) & ", "
For i = 9 To 14
msg = msg & headings(1, i) & ": " & datavar(1, i) & ", "
Next i

msg = msg & "</Html>"
ebody = msg
Debug.Print (ebody)
End If
sendto = datavar(1, 3)
ccto = datavar(1, 4) & datavar(1, 5) & ";kk@abc.com "
' Set App = CreateObject("Outlook.Application")
' Set itm = App.CreateItem(olMailItem)
' With itm

' .Subject = esubject
' .To = sendto
' .CC = ccto

' .HTMLBody = ebody
' .Display
' End With
' Set App = Nothing
' Set itm = Nothing

***:
End Sub

[/vba]
09-27-2016, 10:32 AM
Dah

Thank you very much for your guidance.
One more help.
When I want to alert the mail 7 days prior to the date available in column L then I have written the code like below.
But the email was composed wrongly. Whether I made any mistake ?

Code:

Dim i As Long For i = 2 To rc1 If (ActiveWorkbook.Worksheets(sht1).Cells(i, 7)) = Int(Now) Then Call Fire_mail(i, "Alert Email") ElseIf Int(Now) - (ActiveWorkbook.Worksheets(sht1).Cells(i, 12)) <= 7 Then Call Fire_mail(i, "Alert Prior7") Else: End If Next i
09-27-2016, 02:28 PM
offthelip

You have left out the int function again::
You need to have both variable of the same type in order for the comparison to work properly.

[vba]Dim i As Long
For i = 2 To rc1
If int((ActiveWorkbook.Worksheets(sht1).Cells(i, 7))) = Int(Now) Then
Call Fire_mail(i, "Alert Email")

ElseIf Int(Now) - int((ActiveWorkbook.Worksheets(sht1).Cells(i, 12))) <= 7 Then
Call Fire_mail(i, "Alert Prior7")

Else:
End If
Next i[/vba]