PDA

View Full Version : [SOLVED] Macro not working while sending email



Dah
09-26-2016, 08:40 AM
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

17179

Please help me to debug the code available within excel file
Thank you very much in advance

offthelip
09-26-2016, 09:26 AM
I can see an immediate problem with your "testing " Module.
the line:
If (ActiveWorkbook.Worksheets(sht1).Cells(i, 8)) = Now Then

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 :


If (Int(ActiveWorkbook.Worksheets(sht1).Cells(i, 8))) = Int(Now) Then

note : I haven't looked at the email code

Dah
09-26-2016, 09:47 AM
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.

offthelip
09-26-2016, 03:54 PM
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"

Dah
09-26-2016, 06:55 PM
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.

17184

offthelip
09-27-2016, 12:48 AM
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.


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><p><font color=""red"">"
msg = ""
msg = msg & "Dear Author" & ",<br><br>"
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 & "</font></p></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

Dah
09-27-2016, 10:32 AM
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 ?



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

offthelip
09-27-2016, 02:28 PM
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.

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