|
Joining 2 modules as 1 for email with Excel
Hi, just like my nick I am lost. I need help with Excel in joining 2
modules as 1 for sending out 2 types of email templates to the recipients. Can anyone help me? I have insert in the 2 modules that I would like to form as 1. Ron de Bruin, you would be finding the module familiar as I had almost copied it all off from your website :) Thanks for the help you had rendered in the past by sharing with us useful modules in your Excel website. I really like your work alot. Keep it up (Module 1) Sub TestFile_2() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells (xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "reject" _ And LCase(cell.Offset(0, 2).Value) < "send" Then Set OutMail = OutApp.CreateItem(olMailItem) On Error Resume Next With OutMail .To = cell.Value .Subject = "Thank You" .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "I am sorry you are not liable to resit for your exam." "Yours Sincerely," & vbNewLine & vbNewLine & _ "School Administrator" .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 2).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub (Module 2) Sub testfile_3() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns ("C").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 4).Value) = "Pending" And LCase(cell.Offset(0, 5).Value) < "send" Then Set OutMail = OutApp.CreateItem(olMailItem) On Error Resume Next With OutMail .To = cell.Value .Subject = "Thank You" .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "Your application is currently being reconsidered." & cell.Offset(0, 1).Value & _ "Kindly refer to the blackboard on 31 January for the result outcome." & vbNewLine & vbNewLine & _ "Yours Sincerely," & vbNewLine & vbNewLine & _ "School Administrator" .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 5).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub |
Joining 2 modules as 1 for email with Excel
I don't understand why you want to join the 2 modules?, if you mean RUN the 2 modules you simply CALL the next module before the end of your first code, so the very last bit of your first code would look like this: Code: -------------------- Application.ScreenUpdating = True Call testfile_3 End Sub -------------------- losttoon;183153 Wrote: Hi, just like my nick I am lost. I need help with Excel in joining 2 modules as 1 for sending out 2 types of email templates to the recipients. Can anyone help me? I have insert in the 2 modules that I would like to form as 1. Ron de Bruin, you would be finding the module familiar as I had almost copied it all off from your website :) Thanks for the help you had rendered in the past by sharing with us useful modules in your Excel website. I really like your work alot. Keep it up Code: -------------------- (Module 1) Sub TestFile_2() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells (xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "reject" _ And LCase(cell.Offset(0, 2).Value) < "send" Then Set OutMail = OutApp.CreateItem(olMailItem) On Error Resume Next With OutMail .To = cell.Value .Subject = "Thank You" .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "I am sorry you are not liable to resit for your exam." "Yours Sincerely," & vbNewLine & vbNewLine & _ "School Administrator" .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 2).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub (Module 2) Sub testfile_3() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup For Each cell In Sheets("Sheet1").Columns ("C").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 4).Value) = "Pending" And LCase(cell.Offset(0, 5).Value) < "send" Then Set OutMail = OutApp.CreateItem(olMailItem) On Error Resume Next With OutMail .To = cell.Value .Subject = "Thank You" .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _ "Your application is currently being reconsidered." & cell.Offset(0, 1).Value & _ "Kindly refer to the blackboard on 31 January for the result outcome." & vbNewLine & vbNewLine & _ "Yours Sincerely," & vbNewLine & vbNewLine & _ "School Administrator" .Send 'Or use Display End With On Error GoTo 0 cell.Offset(0, 5).Value = "send" Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50607 |
Joining 2 modules as 1 for email with Excel
On Jan 15, 5:23*pm, Simon Lloyd
wrote: I don't understand why you want to join the 2 modules?, if you mean RUN the 2 modules you simply CALL the next module before the end of your first code, so the very last bit of your first code would look like this: Code: -------------------- * * Application.ScreenUpdating = True * Call testfile_3 * End Sub -------------------- losttoon;183153 Wrote: Hi, just like my nick I am lost. I need help with Excel in joining 2 modules as 1 for sending out 2 types of email templates to the recipients. Can anyone help me? I have insert in the 2 modules that I would like to form as 1. Ron de Bruin, you would be finding the module familiar as I had almost copied it all off from your website :) Thanks for the help you had rendered in the past by sharing with us useful modules in your Excel website. I really like your work alot. Keep it up Code: -------------------- * * * * (Module 1) * Sub TestFile_2() * Dim OutApp As Outlook.Application * Dim OutMail As Outlook.MailItem * Dim cell As Range * * Application.ScreenUpdating = False * Set OutApp = CreateObject("Outlook.Application") * OutApp.Session.Logon * * On Error GoTo cleanup * For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells * (xlCellTypeConstants) * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, * 1).Value) = "reject" _ * And LCase(cell.Offset(0, 2).Value) < "send" Then * Set OutMail = OutApp.CreateItem(olMailItem) * * On Error Resume Next * With OutMail * .To = cell.Value * .Subject = "Thank You" * .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine * & vbNewLine & _ * "I am sorry you are not liable to resit for * your exam." * "Yours Sincerely," & vbNewLine & vbNewLine & * _ * "School Administrator" * .Send 'Or use Display * End With * * On Error GoTo 0 * * cell.Offset(0, 2).Value = "send" * Set OutMail = Nothing * End If * Next cell * * cleanup: * Set OutApp = Nothing * Application.ScreenUpdating = True * End Sub * * (Module 2) * * Sub testfile_3() * Dim OutApp As Outlook.Application * Dim OutMail As Outlook.MailItem * Dim cell As Range * * * Application.ScreenUpdating = False * Set OutApp = CreateObject("Outlook.Application") * OutApp.Session.Logon * * On Error GoTo cleanup * For Each cell In Sheets("Sheet1").Columns * ("C").Cells.SpecialCells(xlCellTypeConstants) * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, * 4).Value) = "Pending" * And LCase(cell.Offset(0, 5).Value) < "send" Then * Set OutMail = OutApp.CreateItem(olMailItem) * * On Error Resume Next * With OutMail * .To = cell.Value * .Subject = "Thank You" * .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine * & vbNewLine & _ * "Your application is currently being * reconsidered." & cell.Offset(0, 1).Value & _ * "Kindly refer to the blackboard on 31 January * for the result outcome." & vbNewLine & vbNewLine & _ * "Yours Sincerely," & vbNewLine & vbNewLine & _ * "School Administrator" * .Send 'Or use Display * End With * * On Error GoTo 0 * * cell.Offset(0, 5).Value = "send" * Set OutMail = Nothing * End If * Next cell * * cleanup: * Set OutApp = Nothing * Application.ScreenUpdating = True * * End Sub * -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=50607 Dear Simon, I had tried but there was no reaction when i tried to run the module. Both modules are in the same excel file and i am trying to make the system understand that when i key in reject, it will auto generate a rejection letter to the applicant and if i enter KIV, it will auto generate another KIV letter to the applicant. I hope you will be able to assist in this as I have not much experience in self creation of marco script and will need your support in the script creation. Thanks for your help :) |
Joining 2 modules as 1 for email with Excel
Why not join our forum for free where you can attach a workbook to your post and we can help you directly with that?, if you do join make sure you post in this thread so that people who have been helping you or following the thread can still do so! losttoon;184784 Wrote: On Jan 15, 5:23*pm, Simon Lloyd wrote: I don't understand why you want to join the 2 modules?, if you mean RUN the 2 modules you simply CALL the next module before the end of your first code, so the very last bit of your first code would look like this: Code: -------------------- * * Application.ScreenUpdating = True * Call testfile_3 * End Sub -------------------- losttoon;183153 Wrote: Hi, just like my nick I am lost. I need help withExcel in joining 2 modules as 1 for sending out 2 types of email templates to the recipients. Can anyone help me? I have insert in the 2 modules that I would like to form as 1. Ron de Bruin, you would be finding the module familiar as I had almost copied it all off from your website :) Thanks for the help you had rendered in the past by sharing with us useful modules in your Excel website. I really like your work alot. Keep it up Code: -------------------- * * * * (Module 1) * Sub TestFile_2() * Dim OutApp As Outlook.Application * Dim OutMail As Outlook.MailItem * Dim cell As Range * * Application.ScreenUpdating = False * Set OutApp = CreateObject("Outlook.Application") * OutApp.Session.Logon * * On Error GoTo cleanup * For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells * (xlCellTypeConstants) * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, * 1).Value) = "reject" _ * And LCase(cell.Offset(0, 2).Value) < "send" Then * Set OutMail = OutApp.CreateItem(olMailItem) * * On Error Resume Next * With OutMail * .To = cell.Value * .Subject = "Thank You" * .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine * & vbNewLine & _ * "I am sorry you are not liable to resit for * your exam." * "Yours Sincerely," & vbNewLine & vbNewLine & * _ * "School Administrator" * .Send 'Or use Display * End With * * On Error GoTo 0 * * cell.Offset(0, 2).Value = "send" * Set OutMail = Nothing * End If * Next cell * * cleanup: * Set OutApp = Nothing * Application.ScreenUpdating = True * End Sub * * (Module 2) * * Sub testfile_3() * Dim OutApp As Outlook.Application * Dim OutMail As Outlook.MailItem * Dim cell As Range * * * Application.ScreenUpdating = False * Set OutApp = CreateObject("Outlook.Application") * OutApp.Session.Logon * * On Error GoTo cleanup * For Each cell In Sheets("Sheet1").Columns * ("C").Cells.SpecialCells(xlCellTypeConstants) * If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, * 4).Value) = "Pending" * And LCase(cell.Offset(0, 5).Value) < "send" Then * Set OutMail = OutApp.CreateItem(olMailItem) * * On Error Resume Next * With OutMail * .To = cell.Value * .Subject = "Thank You" * .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine * & vbNewLine & _ * "Your application is currently being * reconsidered." & cell.Offset(0, 1).Value & _ * "Kindly refer to the blackboard on 31 January * for the result outcome." & vbNewLine & vbNewLine & _ * "Yours Sincerely," & vbNewLine & vbNewLine & _ * "School Administrator" * .Send 'Or use Display * End With * * On Error GoTo 0 * * cell.Offset(0, 5).Value = "send" * Set OutMail = Nothing * End If * Next cell * * cleanup: * Set OutApp = Nothing * Application.ScreenUpdating = True * * End Sub * -------------------- -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread:'Joining 2 modules as 1 for email with Excel - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=50607) Dear Simon, I had tried but there was no reaction when i tried to run the module. Both modules are in the same excel file and i am trying to make the system understand that when i key in reject, it will auto generate a rejection letter to the applicant and if i enter KIV, it will auto generate another KIV letter to the applicant. I hope you will be able to assist in this as I have not much experience in self creation of marco script and will need your support in the script creation. Thanks for your help :) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50607 |
| All times are GMT +1. The time now is 03:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com