GBKEE

¦^´_ 11# missbb

¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub ex()
   
3.     Set d = CreateObject("Scripting.Dictionary")
   
4.     With Worksheets("attendance report")
   
5.         For Each a In .Range(.[E4], .[E4].End(xlDown))
   
6.             d(a.Value) = ""         '¨ú±o©Ò¦³¤£­«½Æ¤À©±
   
7.         Next
   
8.         F = InputBox("Enter your month")
   
9.         For Each ky In d.keys
   
10.             .Range("B4").AutoFilter Field:=4, Criteria1:=ky
    
11.             If Dir("C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf") <> "" Then Kill "C:\Users\mariasfy\Desktop\" & ky & "_" & F & "201507.pdf" '¦P¦WÀɮקR°£
    
12.             .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    
13.             "C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    
14.             IgnorePrintAreas:=False, OpenAfterPublish:=False  '¥t¦s¦¨PDFÀÉ®×
    
15.             '************************************************************
    
16.             SendMail "C:\Users\mariasfy\Desktop\" & ky & "_" & F & ".pdf"
    
17.             '************************************************************
    
18.         Next
    
19.     End With
    
20. End Sub
    
21. Sub SendMail(xFile As String)
    
22.     'VBA ¥\¯àªí«ü¥O: ¤u¨ã->³]©w¤Þ¥Î¶µ¥Ø ·s¼W -> [Microsoft CDO for Windows 2000 Library]
    
23.     '¥i Google   CreateObject("CDO.Message") ¤F¸Ñ
    
24.     Dim objEmail As Object
    
25.     Set objEmail = CreateObject("CDO.Message")      '«Ø¥ß CDO ª«¥ó
    
26.     With objEmail
    
27.         With .Configuration.Fields
    
28.             .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    
29.             .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "msa.hinet.net"              '¨Ï¥Î msa.hinet.net ¶Ç°e¶l¥ó
    
30.             .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    
31.             .Update
    
32.         End With
    
33.         .From = "±H¥óªÌ@pchome.com"           '±H¥óªÌ¡]ºô°ì¥²¶·¦s¦b¡^
    
34.         .To = "¦¬¥óªÌ@gmail.com"
    
35.         .Subject = "CreateObjectCDO.Message CDO¶l¥ó´ú¸Õ"    '¶l¥ó¥D¦®
    
36.         .HTMLBody = "¶l¥ó¥»¤å"   'HTML¶l¥ó¤º¤å
    
37.         .AddAttachment xFile   'ªþÀÉ
    
38.         .Send
    
39.     End With
    
40. End Sub

½Æ»s¥N½X

missbb

¦^´_ 11# GBKEE

Åý§Ú¸Õ¤@¤U, ·P¿E!:'(

missbb

¦^´_ 12# missbb

§Ú¥i¥H±N¤u§@ªíATTENDANCE REPORT V3:AZ3ªº¤é´ÁÂà¸m¨ì¤u§@ªíLEAVE SUMMAy, ¦ýv3:AZ3¦p¦ó¥Hcopy 30¦¸¹ïÀ³¨C­Ó­û¤u, ¦]­û¤u¤£­û1­Ó?

¦p¦ó¥i¥H±N¨C­Ó­û¤uªºATTENDANCE REPORT¤ºªº"°²´Á/¨Ò°²/³Æµù"¦C¤ºªº³Ò©Î¨Ò«ö­û¤u¤Î¤é´Á°t¸m©óleave summaryªºdÄæ©O? ¥Î¨ç¼Æ(IF(ISERROR(INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0))),"",INDEX('ATTENDANCE REPORT'!$1:$1048576,MATCH($A$2,'ATTENDANCE REPORT'!$M:$M,0),MATCH(DAY($C2),'ATTENDANCE REPORT'!$3:$3,0)))¬O¥i¥H°µ¨ì, ©Î¦p¦ó±N¨ç¼Æ¥[¤Jvba¤º?

¥¼§¹ªºvba:
Sheets("attendance report").Range("v3:az3").Copy
Sheets("leave summary").Range("c2").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True
   
·P¿E! Âà¸m¨ç¼Æ.rar (285.08 KB)

¤U¸ü¦¸¼Æ: 7

2015-8-8 23:23